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@xxxxxxxxx>
To: "carter ck" <carterck32@xxxxxxxxxxx>
CC: chad.wagner@xxxxxxxxx, pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] Improve Postgres Query Speed
Date: Mon, 15 Jan 2007 23:19:13 -0200
"carter ck" <carterck32@xxxxxxxxxxx> 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@xxxxxxxxx>
_________________________________________________________________
Get MSN Messenger emoticons and display pictures here!
http://ilovemessenger.msn.com/?mkt=en-sg
