Re: [GENERAL] Improve Postgres Query Speed
On Mon, 2007-01-15 at 23:26, Tom Lane wrote: carter ck [EMAIL PROTECTED] writes: And you are definitely right when updating the table. The time it takes is getting longer and longer. When I do a select statement, the speed has also degraded. Seems like you need a VACUUM in there somewhere... If he hasn't been vacuuming up until now, he'll need a vacuum full. then regular vacuums. ---(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
Re: [GENERAL] Improve Postgres Query Speed
On 1/15/07, carter ck [EMAIL PROTECTED] wrote: I am having slow performance issue when querying a table that contains more than 1 records. Everything just slow down when executing a query though I have created Index on it. You didn't really provide much information for anyone to help you. I would suggest posting the table definition (columns indexes), the queries you are running, and the output of EXPLAIN ANALYZE your query here;. -- Chad http://www.postgresqlforums.com/
Re: [GENERAL] Improve Postgres Query Speed
Hi,
Thanks for reminding me. And the actual number of records is 100,000.
The table is as following:
Table my_messages
midx | integer| not null
default nextval('public.my_messages_midx_seq'::text)
msg_from | character varying(150) |
msg_to | character varying(150) |
msg_content | text |
msg_status | character(1) | default 'N'::bpchar
created_dtm| timestamp without time zone | not null default now()
processed_dtm | timestamp without time zone |
rpt_generated | character(1)| default 'N'::bpchar
Indexes:
msgstat_pkey PRIMARY KEY, btree (midx)
my_messages_msgstatus_index btree (msg_status)
Thanks for help.
From: Chad Wagner [EMAIL PROTECTED]
To: carter ck [EMAIL PROTECTED]
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Improve Postgres Query Speed
Date: Mon, 15 Jan 2007 19:54:51 -0500
On 1/15/07, carter ck [EMAIL PROTECTED] wrote:
I am having slow performance issue when querying a table that contains
more
than 1 records.
Everything just slow down when executing a query though I have created
Index
on it.
You didn't really provide much information for anyone to help you. I would
suggest posting the table definition (columns indexes), the queries you
are running, and the output of EXPLAIN ANALYZE your query here;.
--
Chad
http://www.postgresqlforums.com/
_
Receive MSN Hotmail alerts over SMS!
http://en-asiasms.mobile.msn.com/ac.aspx?cid=1002
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Improve Postgres Query Speed
carter ck wrote: Hi, Thanks for reminding me. And the actual number of records is 100,000. The table is as following: And the query? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Improve Postgres Query Speed
carter ck [EMAIL PROTECTED] writes:
Hi,
Thanks for reminding me. And the actual number of records is 100,000.
The table is as following:
You forgot the EXPLAIN ANALYZE output...
Table my_messages
midx | integer| not null default
nextval('public.my_messages_midx_seq'::text)
msg_from | character varying(150) |
msg_to | character varying(150) |
msg_content | text |
msg_status | character(1) | default 'N'::bpchar
created_dtm| timestamp without time zone | not null default now()
processed_dtm | timestamp without time zone |
rpt_generated | character(1)| default 'N'::bpchar
Is rpt_generated a boolean column?
Indexes:
msgstat_pkey PRIMARY KEY, btree (midx)
my_messages_msgstatus_index btree (msg_status)
If your query doesn't filter with those indices then you won't gain much with
them... E.g. UPDATE my_messages SET rpt_generated='Y' WHERE
rpt_generated='N';
won't use any of those indices and will seq scan the whole table.
--
Jorge Godoy [EMAIL PROTECTED]
---(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
Re: [GENERAL] Improve Postgres Query Speed
Hi, the rpt_generated is a boolean value.
And you are definitely right when updating the table. The time it takes is
getting longer and longer. When I do a select statement, the speed has also
degraded.
Thanks.
From: Jorge Godoy [EMAIL PROTECTED]
To: carter ck [EMAIL PROTECTED]
CC: [EMAIL PROTECTED], pgsql-general@postgresql.org
Subject: Re: [GENERAL] Improve Postgres Query Speed
Date: Mon, 15 Jan 2007 23:19:13 -0200
carter ck [EMAIL PROTECTED] writes:
Hi,
Thanks for reminding me. And the actual number of records is 100,000.
The table is as following:
You forgot the EXPLAIN ANALYZE output...
Table my_messages
midx | integer| not null
default
nextval('public.my_messages_midx_seq'::text)
msg_from | character varying(150) |
msg_to | character varying(150) |
msg_content | text |
msg_status | character(1) | default
'N'::bpchar
created_dtm| timestamp without time zone | not null default
now()
processed_dtm | timestamp without time zone |
rpt_generated | character(1)| default 'N'::bpchar
Is rpt_generated a boolean column?
Indexes:
msgstat_pkey PRIMARY KEY, btree (midx)
my_messages_msgstatus_index btree (msg_status)
If your query doesn't filter with those indices then you won't gain much
with
them... E.g. UPDATE my_messages SET rpt_generated='Y' WHERE
rpt_generated='N';
won't use any of those indices and will seq scan the whole table.
--
Jorge Godoy [EMAIL PROTECTED]
_
Get MSN Messenger emoticons and display pictures here!
http://ilovemessenger.msn.com/?mkt=en-sg
---(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
Re: [GENERAL] Improve Postgres Query Speed
carter ck [EMAIL PROTECTED] writes: Hi, the rpt_generated is a boolean value. Is there any special reason, then, for not using a boolean type? And you are definitely right when updating the table. The time it takes is getting longer and longer. When I do a select statement, the speed has also degraded. And it is expected to be like that if you filter on unindexed columns... Maybe you should go back to design and fix this :-) (Remember that too much indices is also bad.) -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Improve Postgres Query Speed
carter ck wrote: Hi, the rpt_generated is a boolean value. And you are definitely right when updating the table. The time it takes is getting longer and longer. When I do a select statement, the speed has also degraded. If you send us the query that is slow, then I'm sure you'll get some suggestions about how to speed it up. Have you run 'analyze' on the table? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Improve Postgres Query Speed
carter ck [EMAIL PROTECTED] writes: And you are definitely right when updating the table. The time it takes is getting longer and longer. When I do a select statement, the speed has also degraded. Seems like you need a VACUUM in there somewhere... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Improve a query...
Eric G. Miller [EMAIL PROTECTED] writes: Looking for the best way to formulate a query to select the most recent entry for an organization in a table like: Take a look at the SELECT reference page's example for SELECT DISTINCT ON: : For example, : : SELECT DISTINCT ON (location) location, time, report : FROM weatherReports : ORDER BY location, time DESC; : : retrieves the most recent weather report for each location. A tad nonstandard, but bloody effective, particularly if you have an appropriate index in place... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [GENERAL] Improve a query...
Try: SELECT DISTINCT ON (org_id) rpt_id, org_id, period,... FROM reports ORDER BY period DESC; -Original Message- From: Eric G. Miller [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 4:04 AM To: PostgreSQL General Subject: [GENERAL] Improve a query... Looking for the best way to formulate a query to select the most recent entry for an organization in a table like: CREATE TABLE reports ( -- Report Id used to link up related 1:M rather than multi-key rpt_id SERIAL NOT NULL PRIMARY KEY, -- A Unique ID for the organization org_id char(10) NOT NULL CHECK(CHARACTER_LENGTH(op_id) = 10), -- The reporting period period integer NOT NULL -- Various and Sundry ... . . . UNIQUE (org_id,period) ); If max(period) for an organization yields the most recent reporting, I want to get whatever is the most recent report for each organization. This query works, but seems expensive... SELECT a.rpt_id, a.org_id, a.period, ... FROM reports As a INNER JOIN (SELECT b.org_id, max(b.period) As period FROM reports b group by b.org_id) As c ON a.org_id = c.org_id and a.period = c.period; EXPLAIN looks thusly: NOTICE: QUERY PLAN: Merge Join (cost=147.98..164.48 rows=10 width=48) - Sort (cost=69.83..69.83 rows=1000 width=32) - Seq Scan on reports a (cost=0.00..20.00 rows=1000 width=32) - Sort (cost=78.15..78.15 rows=100 width=16) - Subquery Scan c (cost=69.83..74.83 rows=100 width=16) - Aggregate (cost=69.83..74.83 rows=100 width=16) - Group (cost=69.83..72.33 rows=1000 width=16) - Sort (cost=69.83..69.83 rows=1000 width=16) - Seq Scan on reports b (cost=0.00..20.00 rows=1000 width=16) The data is very hierarchical so I didn't want to carry around alot of key fields in related many-sided tables which may also have related many-sided tables. Any ideas on how to minimize the multiple scans on the table? The numbers for explain probably aren't telling much since there's not much real data in the table at this time... -- Eric G. Miller [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
