Tom,
You are absolutely correct about not having run ANALYZE
on the particular table.
In my attempt to create a simple "test case" I created that
table (pk_c2) from the original and had not run ANALYZE
on it, even though, ANALYZE had been run prior to building
that table.
The problem on the test table and the simple select count(*)
is no longer there (after ANALYZE).
The original issue, however, is still there. I'm stumped as
how to formulate my question without having to write a
lengthy essay.
As to upgrading from 7.4, I hear you, but I'm trying to support
a deployed product.
Thanks again for your input,
--patrick
On 4/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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