From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexander Staubo <alex@bengler.no>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plan with high-cardinality column
Date: Fri, 22 Feb 2013 15:33:36 -0500
Message-ID: <7707.1361565216@sss.pgh.pa.us> (raw)
In-Reply-To: <19B2B4ECC4724A438200C54257BA8B20@gmail.com>
References: <19B2B4ECC4724A438200C54257BA8B20@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Alexander Staubo <alex@bengler.no> writes:
> select comments.id from comments where
> conversation_id = 3975979 order by created_at limit 13
> I'm at a loss how to explain why the planner thinks scanning a huge
> index that covers the entire table will ever beat scanning a small index
> that has 17% of the table's values.
The reason is that the LIMIT may stop the query before it's scanned all
of the index. The planner estimates on the assumption that the desired
rows are roughly uniformly distributed within the created_at index, and
on that assumption, it looks like this query will stop fairly soon ...
but evidently, that's wrong. On the other hand, it knows quite well
that the other plan will require pulling out 5000-some rows and then
sorting them before it can return anything, so that's not going to be
exactly instantaneous either.
In this example, I'll bet that conversation_id and created_at are pretty
strongly correlated, and that most or all of the rows with that specific
conversation_id are quite far down the created_at ordering, so that the
search through the index takes a long time to run. OTOH, with another
conversation_id the same plan might run almost instantaneously.
If you're concerned mostly with this type of query then a 2-column index
on (conversation_id, created_at) would serve your purposes nicely. You
could likely even dispense with the separate index on conversation_id
alone.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, alex@bengler.no
Subject: Re: Bad query plan with high-cardinality column
In-Reply-To: <7707.1361565216@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