Improve Postgres Query Speed

On 1/15/07, carter ck <carterck32@hotmail.com> wrote:

I am having slow performance issue when querying a table that contains
more
than 10000 records.

Everything just slow down when executing a query though I have created
Index
on it.

You didn't really provide much information for anyone to help you. I would
suggest posting the table definition (columns & indexes), the queries you
are running, and the output of "EXPLAIN ANALYZE <your query here>;".

--
Chad
http://www.postgresqlforums.com/

Hi,

Thanks for reminding me. And the actual number of records is 100,000.

The table is as following:

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

Indexes:
"msgstat_pkey" PRIMARY KEY, btree (midx)
"my_messages_msgstatus_index" btree (msg_status)

Thanks for help.

From: "Chad Wagner" <chad.wagner@gmail.com>
To: "carter ck" <carterck32@hotmail.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Improve Postgres Query Speed
Date: Mon, 15 Jan 2007 19:54:51 -0500

On 1/15/07, carter ck <carterck32@hotmail.com> wrote:

I am having slow performance issue when querying a table that contains
more
than 10000 records.

Everything just slow down when executing a query though I have created
Index
on it.

You didn't really provide much information for anyone to help you. I would
suggest posting the table definition (columns & indexes), the queries you
are running, and the output of "EXPLAIN ANALYZE <your query here>;".

--
Chad
http://www.postgresqlforums.com/

_________________________________________________________________
Receive MSN Hotmail alerts over SMS!
http://en-asiasms.mobile.msn.com/ac.aspx?cid=1002

carter ck wrote:

Hi,

Thanks for reminding me. And the actual number of records is 100,000.

The table is as following:

And the query?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

"carter ck" <carterck32@hotmail.com> writes:

Hi, the rpt_generated is a boolean value.

Is there any special reason, then, for not using a boolean type?

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.

And it is expected to be like that if you filter on unindexed columns...
Maybe you should go back to design and fix this :-) (Remember that too much
indices is also bad.)

--
Jorge Godoy <jgodoy@gmail.com>

carter ck wrote:

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.

If you send us the query that is slow, then I'm sure you'll get some
suggestions about how to speed it up.

Have you run 'analyze' on the table?

--
Postgresql & php tutorials
http://www.designmagick.com/

"carter ck" <carterck32@hotmail.com> writes:

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.

Seems like you need a VACUUM in there somewhere...

regards, tom lane

On Mon, 2007-01-15 at 23:26, Tom Lane wrote:

"carter ck" <carterck32@hotmail.com> writes:

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.

Seems like you need a VACUUM in there somewhere...

If he hasn't been vacuuming up until now, he'll need a vacuum full.

then regular vacuums.