Skip site navigation (1) Skip section navigation (2)

order by index, and inheritance

From: Michiel Meeuwissen <Michiel(dot)Meeuwissen(at)omroep(dot)nl>
To: performance(at)postgresql(dot)org
Subject: order by index, and inheritance
Date: 2004-04-19 11:30:13
Message-ID: 20040419113013.GA32186@michiel.omroep.nl (view raw or flat)
Thread:
Lists: pgsql-performance
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'                  |
 [] ()                   |

Responses

pgsql-performance by date

Next:From: Rod TaylorDate: 2004-04-19 12:01:15
Subject: Re: very slow simple query - outer join makes it quicker
Previous:From: Gerard IsdellDate: 2004-04-19 10:59:48
Subject: very slow simple query - outer join makes it quicker

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group