Обсуждение: 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.

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.