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: Improve Postgres Query Speed |
Date: | 2007-01-16 01:19:13 |
Message-ID: | 87bqkzkbu6.fsf@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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>
From | Date | Subject | |
---|---|---|---|
Next Message | Takayuki Tsunakawa | 2007-01-16 01:20:04 | Re: [HACKERS] Checkpoint request failed on version 8.2.1. |
Previous Message | Alvaro Herrera | 2007-01-16 01:19:10 | Re: Improve Postgres Query Speed |