From: Michiel Meeuwissen <Michiel.Meeuwissen@omroep.nl>
To: performance@postgresql.org
Subject: order by index, and inheritance
Date: Mon, 19 Apr 2004 13:30:13 +0200
Message-ID: <20040419113013.GA32186@michiel.omroep.nl> (raw)
I have a query which performs not so well:
SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20;
costs nearly a minute. The table contains over 300 000 records.
The table has two extensions, which are (a the moment) nearly empty, but
have something to do with this, because:
SELECT * FROM only mm_mediasources ORDER BY number DESC LIMIT 20;
performs ok (8ms). The query plan is then as I would expect:
media=# explain SELECT * FROM only mm_mediasources ORDER BY number DESC
LIMIT 20;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..8.36 rows=20 width=105)
-> Index Scan Backward using mediasource_object on mm_mediasources
(cost=0.00..114641.05 rows=274318 width=105)
The query plan of the original query, without 'only' does table scans:
media=# explain SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Limit (cost=47248.70..47248.75 rows=20 width=105)
-> Sort (cost=47248.70..47934.52 rows=274328 width=105)
Sort Key: public.mm_mediasources.number
-> Result (cost=0.00..8364.28 rows=274328 width=105)
-> Append (cost=0.00..8364.28 rows=274328 width=105)
-> Seq Scan on mm_mediasources (cost=0.00..8362.18 rows=274318 width=105)
-> Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84)
-> Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.09 rows=9 width=89)
and presumably because if that performs so lousy.
Simply selecting on a number does work fast:
media=# explain SELECT * FROM mm_mediasources where number = 606973 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Result (cost=0.00..6.13 rows=4 width=105)
-> Append (cost=0.00..6.13 rows=4 width=105)
-> Index Scan using mediasource_object on mm_mediasources (cost=0.00..4.00 rows=2 width=105)
Index Cond: (number = 606973)
-> Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84)
Filter: (number = 606973)
-> Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.11 rows=1 width=89)
Filter: (number = 606973)
(3ms)
I suppose seq scans are used on the extensions because they contain so few
records.
All tables have index on number. How do I force it to use them also when I
use order by?
I use psql 7.3.2
Michiel
--
Michiel Meeuwissen |
Mediapark C101 Hilversum |
+31 (0)35 6772979 | I hate computers
nl_NL eo_XX en_US |
mihxil' |
[] () |
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: Michiel.Meeuwissen@omroep.nl, performance@postgresql.org
Subject: Re: order by index, and inheritance
In-Reply-To: <20040419113013.GA32186@michiel.omroep.nl>
* 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