Re: indexes ignored when querying the master table
От | Thomas Hägi |
---|---|
Тема | Re: indexes ignored when querying the master table |
Дата | |
Msg-id | 4DC90362.7070902@refusion.com обсуждение исходный текст |
Ответ на | Re: indexes ignored when querying the master table (Florian Weimer <fweimer@bfk.de>) |
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: