order by index, and inheritance

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' |
[] () |