DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:subject:content-type:content-transfer-encoding;
	b=nuK4ZQziQzYmIcFrOBbNBtidg8L9BMd2MkPTzE8NOg3O/3tannQyF6GtViUnK4eAgAeQ1REGvNaJDpzMFnIsp80c8rkmO1C3C5oKVUjWTDtpAFGypYRL3YL+njR5FXBGnBQOarZtjtZbqYrnCmLbLcVXOkLF40vSa4vmcCLLAi0=
Message-ID: <428B5179.6060202@gmail.com>
Date: Wed, 18 May 2005 16:30:17 +0200
From: =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano@gmail.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: where+orderby+limit not (always) using appropriate index?
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

Dear Gurus,

I don't think it's a bug, I just don't understand what's behind this. If 
there's a paper or something on this, please point me there.

Version: 7.4.6
Locale: hu_HU (in case that matters)
Dump: see below sig.

Abstract:
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?

Problem #2: If I drop the problematic 1-col index, it uses the 2-col index, 
but sorts after that. (and probably that's why the planner was right in #1) Why?

Below is an example that isn't perfect; also, IRL I use a second field of 
type date.

Problem #3: It seems that an opposite index (o, i) works differently but 
still not always. Why?

In case it matters, I may be able to reproduce the original problem with 
original data.

TIA,
--
G.

# CREATE TABLE t(i int, o int);
CREATE TABLE
# CREATE INDEX t_i on t (i);
CREATE INDEX
# CREATE INDEX t_o on t (o);
CREATE INDEX
# CREATE INDEX t_io on t (i, o);
CREATE INDEX
# INSERT INTO t SELECT 1, p.oid::int FROM pg_proc p WHERE Pronamespace=11;
INSERT 0 1651
# explain analyze select * from t where i=1 order by o limit 1;
                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..3.37 rows=1 width=8) (actual time=0.028..0.029 rows=1 
loops=1)
    ->  Index Scan using t_o on t  (cost=0.00..20.20 rows=6 width=8) (actual 
time=0.025..0.025 rows=1 loops=1)
          Filter: (i = 1)
  Total runtime: 0.082 ms
(4 rows)

# drop index t_o;
DROP INDEX
# explain analyze select * from t where i=1 order by o limit 1;
                                                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
  Limit  (cost=6.14..6.14 rows=1 width=8) (actual time=4.624..4.625 rows=1 
loops=1)
    ->  Sort  (cost=6.14..6.15 rows=6 width=8) (actual time=4.619..4.619 
rows=1 loops=1)
          Sort Key: o
          ->  Index Scan using t_io on t  (cost=0.00..6.11 rows=6 width=8) 
(actual time=0.026..2.605 rows=1651 loops=1)
                Index Cond: (i = 1)
  Total runtime: 4.768 ms
(6 rows)

[local]:tir=#