Re: Huge Performance Difference on Similar Query in Pg7.2

On Fri, 22 Mar 2002 13:35:47 +0000 (GMT)
"Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)uk> wrote:

> Show looking up in large table, selecting on partial primary key, uses
> sequential scan on large
>
> explain analyze select count(*) from chat_post cp where cp.session_id
> = 123;
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=20411.68..20411.68 rows=1 width=0) (actual
> time=31691.92..31691.93 rows=1 loops=1)
> -> Seq Scan on chat_post cp (cost=0.00..20411.49 rows=77 width=0)
> (actual time=1736.29..31688.80 rows=321 loops=1)
> Total runtime: 31692.35 msec

Judging from the output of the EXPLAIN, queries--which include a chat_post
that is limited by session_id=123--always seem to use a sequential scan
on it. On the other hand, other queries--which include one that isn't--seem
to use an index scan on one. Therefore, instead of session_id=123, you may
as well execute a series of your queries again with other conditions which
will use an index scan. But, this opinion is not based on any real evidence.

BTW, even though the number of selected rows in a chat_post is small,
aggregating and sorting time seem to be long. if sort_mem is a default
value, before trying above, you need to increase it -- possibly 10 or 20 times.

Regards,
Masaru Sugawara