To: "patrick keshishian" <pkeshish@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Planner doesn't chose Index - (slow select) 
In-reply-to: <53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com> 
References: <53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com>
Comments: In-reply-to "patrick keshishian" <pkeshish@gmail.com>
	message dated "Tue, 18 Apr 2006 18:02:27 -0700"
Date: Tue, 18 Apr 2006 22:19:44 -0400
Message-ID: <20957.1145413184@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"patrick keshishian" <pkeshish@gmail.com> writes:
> I've been struggling with some performance issues with certain
> SQL queries.  I was prepping a long-ish overview of my problem
> to submit, but I think I'll start out with a simple case of the
> problem first, hopefully answers I receive will help me solve
> my initial issue.

Have you ANALYZEd this table lately?

> db=# select count(*) from pk_c2 b0 where b0.offer_id=7141;
>  count
> -------
>      1
> (1 row)

The planner is evidently estimating that there are 12109 such rows,
not 1, which is the reason for its reluctance to use an indexscan.
Generally the only reason for it to be off that far on such a simple
statistical issue is if you haven't updated the stats in a long time.
(If you've got a really skewed data distribution for offer_id, you
might need to raise the statistics target for it.)

> The table has indexes for both 'offer_id' and '(pending=true)':

> Indexes:
>     "pk_boidx" btree (offer_id)
>     "pk_bpidx" btree (((pending = true)))

The expression index on (pending = true) won't do you any good,
unless you spell your query in a weird way like
	... WHERE (pending = true) = true
I'd suggest a plain index on "pending" instead.

> db=# select version();
>  PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6

You might want to think about an update, too.  7.4 is pretty long in the
tooth.

			regards, tom lane