From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nowak Michał <michal.nowak@me.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query with order by and limit is very slow - wrong index used
Date: Mon, 03 Oct 2011 11:12:31 -0400
Message-ID: <15615.1317654751@sss.pgh.pa.us> (raw)
In-Reply-To: <6B8FADC8-9692-439F-8AA4-0CAA287FCEB0@me.com>
References: <6B8FADC8-9692-439F-8AA4-0CAA287FCEB0@me.com>
=?iso-8859-2?Q?Nowak_Micha=B3?= <michal.nowak@me.com> writes:
> When I perform query such as this: "select * from records where source_id = 'XXX' order by id limit 200;" I expect DB to use index source_id_id_idx with XXX as filter. It is true for all but one values of XXX - when I ask for records with most common source_id, records_pkey index is used instead and performance is terrible! Explain analyze results below.
I'm thinking it probably sees the pkey index as cheaper because that's
highly correlated with the physical order of the table. (It would be
useful to see pg_stats.correlation for these columns.) With a
sufficiently unselective filter, scanning in pkey order looks cheaper
than scanning in source_id order.
If so, what you probably need to do to get the estimates more in line
with reality is to reduce random_page_cost. That will reduce the
assumed penalty for non-physical-order scanning.
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, michal.nowak@me.com
Subject: Re: Query with order by and limit is very slow - wrong index used
In-Reply-To: <15615.1317654751@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