order by index, and inheritance - Mailing list 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'                  |
 [] ()                   |