date:20060420

Re: [PERFORM] Takes too long to fetch the data from database

2006-04-20 Thread Bruno Wolff III

On Thu, Apr 20, 2006 at 11:07:31 +0530,
  soni de [EMAIL PROTECTED] wrote:
 Please provide me some help regarding how could I use cursor in following
 cases? :
 
 I want to fetch 50 records at a time starting from largest stime.
 
 SELECT * FROM wan ORDER BY stime LIMIT 50 OFFSET 81900;

Something like the following may be faster:
SELECT * FROM wan ORDER BY stime DESC LIMIT 50;

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[PERFORM] Perfrmance Problems (7.4.6)

2006-04-20 Thread Doron Baranes


Hi,

I am running on postgres 7.4.6.
I did a vacuum analyze on the database but there was no change.
I Attached here a file with details about the tables, the queries and
the Explain analyze plans.
Hope this can be helpful to analyze my problem

10x
Doron
TABLES


Table log.msg_info
   Column   |Type |Modifiers
+-+--
 msgid  | bigint  | not null default 
nextval('log.msg_info_msgid_seq'::text)
 sender | character varying(255)  |
 subject| text|
 size   | bigint  |
 entry_time | timestamp without time zone | default now()
 source_ip  | cidr|
 origin | smallint|
Indexes:
msg_info_pkey primary key, btree (msgid)
ddindx btree (date(entry_time))
msg_info_entry_time btree (entry_time)
msg_info_sender_index btree (sender)
msg_info_size btree (size)
msg_info_subject btree (subject)

 Table log.msg_fate
   Column|  Type  |   Modifiers
-++
 msgid   | bigint | not null default 
nextval('log.msg_fate_msgid_seq'::text)
 grp_fate_id | bigint | not null default 
nextval('log.msg_fate_grp_fate_id_seq'::text)
 modid   | integer|
 description | character varying(255) |
 rule_origin | bigint |
 action  | smallint   |
 ruleid  | integer|
Indexes:
msg_fate_pkey primary key, btree (grp_fate_id)
msg_fate_action btree (action)
msg_fate_description btree (description)
msg_fate_modid btree (modid)
msg_fate_msgid btree (msgid)
Foreign-key constraints:
msgid_fkey FOREIGN KEY (msgid) REFERENCES log.msg_info(msgid) ON UPDATE 
CASCADE ON DELETE CASCADE

  Table log.msg_fate_recipients
   Column|Type |   Modifiers
-+-+---
 grp_fate_id | bigint  |
 recipient   | character varying(255)  |
 update_at   | timestamp without time zone | default now()
 last_action | integer |
 zone_id | integer |
 direction   | smallint| default 7
Indexes:
msg_fate_recipients_grp_fate_id btree (grp_fate_id)
msg_fate_recipients_last_action_idx btree (last_action)
msg_fate_recipients_recipient_idx btree (recipient)
msg_fate_recipients_update_at btree (update_at)
msg_fate_recipients_zone_id btree (zone_id)
Triggers:
stats_for_domain AFTER INSERT ON log.msg_fate_recipients FOR EACH ROW 
EXECUTE PROCEDURE log.collect_stats_for_domain()
stats_for_object AFTER INSERT ON log.msg_fate_recipients FOR EACH ROW 
EXECUTE PROCEDURE log.collect_stats_for_object()
update_timestamp_last_action BEFORE UPDATE ON log.msg_fate_recipients FOR 
EACH ROW EXECUTE PROCEDURE log.recipients_status_changed_update()

  Table pineapp.zones
  Column   |  Type  |  Modifiers
---++-
 zone_id   | integer| not null default 
nextval('pineapp.zones_zone_id_seq'::text)
 zone_name | character varying(20)  |
 zone_desc | character varying(255) |
 zone_type | smallint   |
Indexes:
zones_pkey primary key, btree (zone_id)
zones_zone_id btree (zone_id)

QUERIES
***


1)
explain analyze SELECT date_trunc('hour'::text, i.entry_time) AS datetime,
COUNT(fr.grp_fate_id) ,
SUM(i.size)
FROM log.msg_info as i,log.msg_fate as f, log.msg_fate_recipients as fr
WHERE i.origin = 1
AND i.msgid=f.msgid
AND i.entry_time  '2006-01-25'
AND f.grp_fate_id=fr.grp_fate_id
GROUP BY datetime
order by datetime;

 QUERY 
PLAN  
-
 GroupAggregate  (cost=1355984.84..1417243.22 rows=1485233 width=24) (actual 
time=257433.784..269102.088 rows=623 loops=1)
   -  Sort  (cost=1355984.84..1368514.62 rows=5011913 width=24) (actual 
time=257349.038..261012.595 rows=5160187 loops=1)
 Sort Key: date_trunc('hour'::text, i.entry_time)
 -  Hash Join  (cost=256729.52..667400.86 rows=5011913 width=24) 
(actual time=63133.140..208966.342 rows=5160187 loops=1)
   Hash Cond: (outer.grp_fate_id = inner.grp_fate_id)
   -  Seq Scan on msg_fate_recipients fr  (cost=0.00..178230.71

Re: [PERFORM] Perfrmance Problems (7.4.6)

2006-04-20 Thread Ruben Rubio Rey

I think that the problem is the GROUP BY (datetime) that is 
date_trunc('hour'::text, i.entry_time)

You should create an indexe with this expression (if its possible).

http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html

If is not possible, I would create a column with value 
date_trunc('hour'::text, i.entry_time) of each row and then index it.


Hope this helps :)

Doron Baranes wrote:


Hi,

I am running on postgres 7.4.6.
I did a vacuum analyze on the database but there was no change.
I Attached here a file with details about the tables, the queries and
the Explain analyze plans.
Hope this can be helpful to analyze my problem

10x
Doron
 





---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match
 




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] Perfrmance Problems (7.4.6)

2006-04-20 Thread Doron Baranes

Ok. But that means I need a trigger on the original column to update the
new column on each insert/update and that overhead.

-Original Message-
From: Ruben Rubio Rey [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 20, 2006 12:49 PM
To: Doron Baranes; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Perfrmance Problems (7.4.6)

I think that the problem is the GROUP BY (datetime) that is 
date_trunc('hour'::text, i.entry_time)
You should create an indexe with this expression (if its possible).

http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html

If is not possible, I would create a column with value 
date_trunc('hour'::text, i.entry_time) of each row and then index it.

Hope this helps :)

Doron Baranes wrote:

Hi,

I am running on postgres 7.4.6.
I did a vacuum analyze on the database but there was no change.
I Attached here a file with details about the tables, the queries and
the Explain analyze plans.
Hope this can be helpful to analyze my problem

10x
Doron

---
-

---(end of
broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[PERFORM] Quick Performance Poll

2006-04-20 Thread Simon Dale

Hi,

I was just wondering whether anyone has had success with
storing more than 1TB of data with PostgreSQL and how they have found the
performance.

We need a database that can store in excess of this amount
and still show good performance. We will probably be implementing several
tables with foreign keys and also indexes which will obviously impact on both
data size and performance too.

Many thanks in advance,

Simon

Visit our Website at www.rm.com

This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RM does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RM. If this email has come to you in error, please delete it, along with any attachments. Please note that RM may intercept incoming and outgoing email communications.

Freedom of Information Act 2000

This email and any attachments may contain confidential information belonging to RM. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RM and the disclosure of which would be prejudicial to RM's commercial interests.

51 matches

Mail list logo