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

> I've read lots of messages on this subject, and the FAQ, recently but I'm still
> confused. None of what I have read seems to account for the execution plans I'm
> seeing, except for this mention of scanning a million values in an index and
> discarding all but a small number. However, even this I can not see applies to
> a primary key on a million row table. I upgraded from 7.0.x to 7.2 because I
> was thinking it was an oddity that was probably fixed.
>
> First, my apologies for the length of this posting.
>
> Next some background:
>
> I have a database where one of it's tables records 'sessions', called
> chat_sessions. It has an integer field, session_id, declared as primary key.
> This table is the small table in the example with only about 2000 rows.
>
> There is another table called chat_post, the large table holding about 1
> million rows. It has two integer fields, session_id and post_number, which

Are they actually integers (int4), or are either of them a different type
like int2 or int8? There are special case workarounds for those two due
to a problem with the types of integer literals. This looks likely since
even with seq_scan set off it wanted to do a sequence scan which generally
means it doesn't believe it can use the index.

> explain analyze select count(*) from chat_post cp where cp.session_id
> = 123;

Does cp.session_id='123' give something different?

> Show looking up in large table, selecting on primary key, uses
> sequential scan on large
>
> explain analyze select count(*) from chat_post cp where cp.session_id
> = 123 and cp.post_number = 10;

Same here for '123' and '10'.