From: Tom Lane <tgl@sss.pgh.pa.us>
To: patrick keshishian <pkeshish@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner doesn't chose Index - (slow select)
Date: Tue, 18 Apr 2006 22:19:44 -0400
Message-ID: <20957.1145413184@sss.pgh.pa.us> (raw)
In-Reply-To: <53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com>
References: <53b425b00604181802v2cc259e1r59f9043b2cea3324@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pkeshish@gmail.com
Subject: Re: Planner doesn't chose Index - (slow select)
In-Reply-To: <20957.1145413184@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox