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(at)gmail(dot)com>
>To: "carter ck" <carterck32(at)hotmail(dot)com>
>CC: chad(dot)wagner(at)gmail(dot)com, pgsql-general(at)postgresql(dot)org
>Subject: Re: [GENERAL] Improve Postgres Query Speed
>Date: Mon, 15 Jan 2007 23:19:13 -0200
>
>"carter ck" <carterck32(at)hotmail(dot)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(at)gmail(dot)com>

_________________________________________________________________
Get MSN Messenger emoticons and display pictures here!
http://ilovemessenger.msn.com/?mkt=en-sg