hi florian
sorry for the late reply - it took almost a day to dump & reload the
data into 9.1b1.
>> how can i get postgres to use the indexes when querying the master
>> table?
>
> I believe that this is a new feature in PostgreSQL 9.1 ("Allow
> inheritance table queries to return meaningfully-sorted results").
you are right, pgsql 9.1 indeed makes use of the indexes now:
EXPLAIN ANALYZE SELECT * FROM data.logs
ORDER BY re_timestamp DESC LIMIT 100;
--------
Limit (cost=11.63..36.45 rows=100 width=1390) (actual time=0.169..0.639
rows=100 loops=1)
-> Result (cost=11.63..6421619.07 rows=25870141 width=1390) (actual
time=0.154..0.610 rows=100 loops=1)
-> Merge Append (cost=11.63..6421619.07 rows=25870141
width=1390) (actual time=0.150..0.429 rows=100 loops=1)
Sort Key: data.logs.re_timestamp
-> Sort (cost=11.46..11.56 rows=40 width=1776) (actual
time=0.014..0.014 rows=0 loops=1)
Sort Key: data.logs.re_timestamp
Sort Method: quicksort Memory: 25kB
-> Seq Scan on logs (cost=0.00..10.40 rows=40
width=1776) (actual time=0.003..0.003 rows=0 loops=1)
-> Index Scan Backward using logs_2003_timestamp_idx on
logs_2003 logs (cost=0.00..373508.47 rows=1825026 width=1327) (actual
time=0.026..0.026 rows=1 loops=1)
-> Index Scan Backward using logs_2004_timestamp_idx on
logs_2004 logs (cost=0.00..417220.55 rows=2034041 width=1327) (actual
time=0.012..0.012 rows=1 loops=1)
-> Index Scan Backward using logs_2005_timestamp_idx on
logs_2005 logs (cost=0.00..502664.57 rows=2438968 width=1345) (actual
time=0.015..0.015 rows=1 loops=1)
-> Index Scan Backward using logs_2006_timestamp_idx on
logs_2006 logs (cost=0.00..640419.01 rows=3091214 width=1354) (actual
time=0.015..0.015 rows=1 loops=1)
-> Index Scan Backward using logs_2007_timestamp_idx on
logs_2007 logs (cost=0.00..752875.00 rows=3603739 width=1369) (actual
time=0.009..0.009 rows=1 loops=1)
-> Index Scan Backward using logs_2008_timestamp_idx on
logs_2008 logs (cost=0.00..969357.51 rows=4406653 width=1440) (actual
time=0.007..0.007 rows=1 loops=1)
-> Index Scan Backward using logs_2009_timestamp_idx on
logs_2009 logs (cost=0.00..862716.39 rows=3986473 width=1422) (actual
time=0.016..0.016 rows=1 loops=1)
-> Index Scan Backward using logs_2010_timestamp_idx on
logs_2010 logs (cost=0.00..778529.29 rows=3579586 width=1426) (actual
time=0.009..0.009 rows=1 loops=1)
-> Index Scan Backward using logs_2011_timestamp_idx on
logs_2011 logs (cost=0.00..200253.71 rows=904401 width=1453) (actual
time=0.006..0.089 rows=100 loops=1)
Total runtime: 1.765 ms
thanks for your help,
thomas
