pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere+orderby+limit not (always) using appropriate index?
3+ messages / 3 participants
[nested] [flat]
* where+orderby+limit not (always) using appropriate index?
@ 2005-05-18 14:30 Szűcs Gábor <surrano@gmail.com>
2005-05-18 15:06 ` Re: where+orderby+limit not (always) using appropriate index? PFC <lists@boutiquenumerique.com>
2005-05-18 15:14 ` Re: where+orderby+limit not (always) using appropriate index? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Szűcs Gábor @ 2005-05-18 14:30 UTC (permalink / raw)
To: pgsql-performance
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=#
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: where+orderby+limit not (always) using appropriate index?
2005-05-18 14:30 where+orderby+limit not (always) using appropriate index? Szűcs Gábor <surrano@gmail.com>
@ 2005-05-18 15:06 ` PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 3+ messages in thread
From: PFC @ 2005-05-18 15:06 UTC (permalink / raw)
To: Szűcs Gábor <surrano@gmail.com>; pgsql-performance
> SELECT o FROM t WHERE i = 1 ORDER BY o LIMIT 1;
use :
ORDER BY i, o
If you have a multicol index and want to order on it, you should help the
planner by ORDERing BY all of the columns in the index...
It bit me a few times ;)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: where+orderby+limit not (always) using appropriate index?
2005-05-18 14:30 where+orderby+limit not (always) using appropriate index? Szűcs Gábor <surrano@gmail.com>
@ 2005-05-18 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-05-18 15:14 UTC (permalink / raw)
To: Szűcs Gábor <surrano@gmail.com>; +Cc: pgsql-performance
=?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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-05-18 15:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-18 14:30 where+orderby+limit not (always) using appropriate index? Szűcs Gábor <surrano@gmail.com>
2005-05-18 15:06 ` PFC <lists@boutiquenumerique.com>
2005-05-18 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
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