From: Thomas Hägi <th@refusion.com>
To: Florian Weimer <fweimer@bfk.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: indexes ignored when querying the master table
Date: Tue, 10 May 2011 11:20:34 +0200
Message-ID: <4DC90362.7070902@refusion.com> (raw)
In-Reply-To: <82ei4871cx.fsf@mid.bfk.de>
References: <4DC46470.20004@refusion.com>
<82ei4871cx.fsf@mid.bfk.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: th@refusion.com, fweimer@bfk.de
Subject: Re: indexes ignored when querying the master table
In-Reply-To: <4DC90362.7070902@refusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox