DomainKey-Signature: a=rsa-sha1; c=nofws; d=refusion.com; s=refusion;
	h=message-id:date:from:organization:user-agent:mime-version:to
	:subject:content-type:content-transfer-encoding;
	b=jHlrkUAAWln1qpXiAnhnayfevqfodEJf3myDOIbgMNFpSANj1NOpaNMx1sHy721EEo
	CceLOSA5rFpBvR5/HcfRisqpsSJibzzWioInYcndwiLIJJ0AovB+abj9w3+wGPQqs1r2
	AfPiT2kMbNW6MZ7tMXr0D+Ok768Uf8vBTmd4Q=
Message-ID: <4DC46470.20004@refusion.com>
Date: Fri, 06 May 2011 23:13:20 +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: pgsql-performance@postgresql.org
Subject: indexes ignored when querying the master table
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

i have around 25mio records of data distributed yearly over 9 child 
tables (data.logs_20xx) that inherit from the master table data.logs. 
the tables are partitioned using the field "re_timestamp", which has 
btree indexes defined on all tables.

the query "SELECT * FROM data.logs ORDER BY re_timestamp DESC LIMIT 100" 
does use seq scans on all tables instead of using the existing indexes 
which takes ages. when issuing the the same query to one of the child 
tables directly ("SELECT * FROM data.logs_2011 ORDER BY re_timestamp 
DESC LIMIT 100") the index is used as expected and the data returned 
quickly.

how can i get postgres to use the indexes when querying the master table?

please find below the EXPLAIN ANALYZE output for both queries on my 
development machine (pgsql 9.0 x64 on windows 7).

thanks in advance,
thomas


EXPLAIN ANALYZE SELECT * FROM data.logs
ORDER BY re_timestamp DESC LIMIT 100;

Limit  (cost=6331255.90..6331256.15 rows=100 width=1388) (actual 
time=1592287.794..1592287.808 rows=100 loops=1)
   ->  Sort  (cost=6331255.90..6395928.37 rows=25868986 width=1388) 
(actual time=1592287.789..1592287.796 rows=100 loops=1)
         Sort Key: data.logs.re_timestamp
         Sort Method:  top-N heapsort  Memory: 217kB
         ->  Result  (cost=0.00..5342561.86 rows=25868986 width=1388) 
(actual time=0.026..1466420.868 rows=25870101 loops=1)
               ->  Append  (cost=0.00..5342561.86 rows=25868986 
width=1388) (actual time=0.020..1417490.892 rows=25870101 loops=1)
                     ->  Seq Scan on logs  (cost=0.00..10.40 rows=40 
width=1776) (actual time=0.002..0.002 rows=0 loops=1)
                     ->  Seq Scan on logs_2011 logs 
(cost=0.00..195428.00 rows=904800 width=1449) (actual 
time=0.017..92381.769 rows=904401 loops=1)
                     ->  Seq Scan on logs_2010 logs 
(cost=0.00..759610.67 rows=3578567 width=1426) (actual 
time=23.996..257612.143 rows=3579586 loops=1)
                     ->  Seq Scan on logs_2009 logs 
(cost=0.00..841998.35 rows=3987235 width=1423) (actual 
time=12.921..200385.903 rows=3986473 loops=1)
                     ->  Seq Scan on logs_2008 logs 
(cost=0.00..942810.60 rows=4409860 width=1444) (actual 
time=18.861..226867.499 rows=4406653 loops=1)
                     ->  Seq Scan on logs_2007 logs 
(cost=0.00..730863.69 rows=3600569 width=1359) (actual 
time=14.406..174082.413 rows=3603739 loops=1)
                     ->  Seq Scan on logs_2006 logs 
(cost=0.00..620978.29 rows=3089929 width=1348) (actual 
time=21.647..147244.677 rows=3091214 loops=1)
                     ->  Seq Scan on logs_2005 logs 
(cost=0.00..486928.59 rows=2440959 width=1342) (actual 
time=0.005..126479.314 rows=2438968 loops=1)
                     ->  Seq Scan on logs_2004 logs 
(cost=0.00..402991.92 rows=2031092 width=1327) (actual 
time=23.007..98531.883 rows=2034041 loops=1)
                     ->  Seq Scan on logs_2003 logs 
(cost=0.00..360941.35 rows=1825935 width=1325) (actual 
time=20.220..91773.705 rows=1825026 loops=1)
Total runtime: 1592293.267 ms


EXPLAIN ANALYZE SELECT * FROM data.logs_2011
ORDER BY re_timestamp DESC LIMIT 100;

Limit  (cost=0.00..22.65 rows=100 width=1449) (actual 
time=59.161..60.226 rows=100 loops=1)
   ->  Index Scan Backward using logs_fts_2011_timestamp_idx on 
logs_2011  (cost=0.00..204919.30 rows=904800 width=1449) (actual 
time=59.158..60.215 rows=100 loops=1)
Total runtime: 60.316 ms