From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joshua Shanks <jjshanks@gmail.com>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: query planner not using the correct index
Date: Thu, 07 Aug 2008 17:12:40 -0400
Message-ID: <27696.1218143560@sss.pgh.pa.us> (raw)
In-Reply-To: <84f0acdb0808070811l43a3da9eq240d272f8ddd105c@mail.gmail.com>
References: <84f0acdb0808061435q5ebb607cjc311f1eb4da841a@mail.gmail.com>
<489A623A.4090400@postnewspapers.com.au>
<84f0acdb0808062014p29cf7482ied332225a0a92c63@mail.gmail.com>
<489A6AF5.50908@postnewspapers.com.au>
<84f0acdb0808070811l43a3da9eq240d272f8ddd105c@mail.gmail.com>
"Joshua Shanks" <jjshanks@gmail.com> writes:
> It seems obvious that the stats on attr1 at the current level are
> inaccurate as there are over 100,000 unique enteries in the table.
Well, you haven't told us how big any of these tables are, so it's
hard to tell if the n_distinct value is wrong or not ... but in
any case I don't think that the stats on attr1 have anything to do
with your problem. The reason that the "fast" query is fast is that
it benefits from the fact that there's only one bars row with
bars_id = 12345. So the question is how many such rows does the
planner now think there are (try "explain analyze select * from bars
where bars_id = 12345"), and if it's badly wrong, then you need to be
looking at the stats on bars.bars_id to find out why.
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, jjshanks@gmail.com, craig@postnewspapers.com.au
Subject: Re: query planner not using the correct index
In-Reply-To: <27696.1218143560@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