DomainKey-Signature: a=rsa-sha1; c=nofws; d=refusion.com; s=refusion;
	h=message-id:date:from:organization:user-agent:mime-version:to:cc
	:subject:references:in-reply-to:content-type
	:content-transfer-encoding;
	b=Wrs5jlRM42OvXPi9hqm8DpFspcibLNOh4EfLh+IsbW3avoPIMCI3V9dRepw1ZcrGDJ
	EAkPBcwC+iLR1ZIlXZiX8/m1XX4jXg5h0xpDzo67YuivfjAuPIGDif/0OhG4O4v9VMUa
	AJyZHyAHqYXA5CyLTpeeFWVeQkzTgml3HQTFI=
Message-ID: <4DC90362.7070902@refusion.com>
Date: Tue, 10 May 2011 11:20:34 +0200
From: =?UTF-8?B?VGhvbWFzIEjDpGdp?= <th@refusion.com>
Organization: REFUSiON GmbH, Stans, Switzerland
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
To: Florian Weimer <fweimer@bfk.de>
CC: pgsql-performance@postgresql.org
Subject: Re: indexes ignored when querying the master table
References: <4DC46470.20004@refusion.com> <82ei4871cx.fsf@mid.bfk.de>
In-Reply-To: <82ei4871cx.fsf@mid.bfk.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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