Thread: Improve Postgres Query Speed
Hi all, I am having slow performance issue when querying a table that contains more than 10000 records. Everything just slow down when executing a query though I have created Index on it. Can anyone suggest ways to improve the speed? Thanks. _________________________________________________________________ Find just what you are after with the more precise, more powerful new MSN Search. http://search.msn.com.sg/ Try it now.
On 1/15/07, carter ck <carterck32@hotmail.com> wrote:
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/
I am having slow performance issue when querying a table that contains more
than 10000 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/
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" <chad.wagner@gmail.com>
>To: "carter ck" <carterck32@hotmail.com>
>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 <carterck32@hotmail.com> wrote:
>>
>>I am having slow performance issue when querying a table that contains
>>more
>>than 10000 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
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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
"carter ck" <carterck32@hotmail.com> 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 <jgodoy@gmail.com>
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 <jgodoy@gmail.com>
>To: "carter ck" <carterck32@hotmail.com>
>CC: chad.wagner@gmail.com, pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Improve Postgres Query Speed
>Date: Mon, 15 Jan 2007 23:19:13 -0200
>
>"carter ck" <carterck32@hotmail.com> 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 <jgodoy@gmail.com>
_________________________________________________________________
Get MSN Messenger emoticons and display pictures here!
http://ilovemessenger.msn.com/?mkt=en-sg
"carter ck" <carterck32@hotmail.com> 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 <jgodoy@gmail.com>
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/
"carter ck" <carterck32@hotmail.com> 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
On Mon, 2007-01-15 at 23:26, Tom Lane wrote: > "carter ck" <carterck32@hotmail.com> 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.