From: Thomas Hägi <th@refusion.com>
To: pgsql-performance@postgresql.org
Subject: indexes ignored when querying the master table
Date: Fri, 06 May 2011 23:13:20 +0200
Message-ID: <4DC46470.20004@refusion.com> (raw)
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
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
Subject: Re: indexes ignored when querying the master table
In-Reply-To: <4DC46470.20004@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