Re: 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 <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