From: Tom Lane <tgl@sss.pgh.pa.us>
To: Szűcs Gábor <surrano@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: where+orderby+limit not (always) using appropriate index?
Date: Wed, 18 May 2005 11:14:39 -0400
Message-ID: <21957.1116429279@sss.pgh.pa.us> (raw)
In-Reply-To: <428B5179.6060202@gmail.com>
References: <428B5179.6060202@gmail.com>
=?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano@gmail.com> writes:
> Create a table with (at least) two fields, say i and o.
> Create three indexes on (i), (o), (i,o)
> Insert enough rows to test.
> Try to replace min/max aggregates with indexable queries such as:
> SELECT o FROM t WHERE i = 1 ORDER BY o LIMIT 1;
> Problem #1: This tends to use one of the single-column indexes (depending on
> the frequency of the indexed element), not the two-column index. Also, I'm
> not perfectly sure but maybe the planner is right. Why?
To get the planner to use the double-column index, you have to use an
ORDER BY that matches the index, eg
SELECT o FROM t WHERE i = 1 ORDER BY i,o LIMIT 1;
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, surrano@gmail.com
Subject: Re: where+orderby+limit not (always) using appropriate index?
In-Reply-To: <21957.1116429279@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