Re: Improve Postgres Query Speed

"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>