Message-ID: <47BAB556.7060506@cat.co.za>
Date: Tue, 19 Feb 2008 12:54:14 +0200
From: Stuart Brooks <stuartb@cat.co.za>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Am I wasting my time with partitions?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

It seems to me that postgresql doesn't use indexes when being asked for 
an ordered result sets from a partitioned table. I have an application 
where this is critical, but I was hoping to use partitions because of 
the ease of rotating out old rows.

Simply put, I have a table called LineItems which I need to be able to 
page from and so I need to be able to ask for N rows ordered on a 
certain index (with possible constraints).

eg. SELECT * FROM T ORDER BY col1,col2 LIMIT 10;

This works fine and is quick on a single table:


 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
metadb=> \d lineitems
                  Table "test2.lineitems"
    Column    |              Type              | Modifiers
--------------+--------------------------------+-----------
 lineitem_key | bigint                         | not null
 time         | timestamp(6) without time zone | not null
 description  | text                           | not null
 barcode      | text                           | not null
 amount       | bigint                         | not null
Indexes:
    "lineitems_amount_index" btree (amount, lineitem_key)

metadb=> explain select * from lineitems order by amount,lineitem_key 
limit 10;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.74 rows=10 width=49)
   ->  Index Scan using lineitems_amount_index on lineitems  
(cost=0.00..39791.76 rows=535500 width=49)
(2 rows)
 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


If I partition the table by creating a top level table L, and inherited 
tables L1, L2 and issue the same request it does sequential scans on all 
the tables and takes orders of magnitude longer (see below).

In the example below I would have hoped that it would have used an index 
scan on each of the tables returning 10 rows each and then done a merge 
on them. Am I asking too much? Should I just use a single table and take 
the hits on deletes and vacuums?

Regards
 Stuart


 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
metadb=> \d L
                      Table "test2.l"
    Column    |              Type              | Modifiers
--------------+--------------------------------+-----------
 lineitem_key | bigint                         | not null
 time         | timestamp(6) without time zone | not null
 description  | text                           | not null
 barcode      | text                           | not null
 amount       | bigint                         | not null
Indexes:
    "l_amount_index" btree (amount, lineitem_key)

metadb=> \d L1
                     Table "test2.l1"
    Column    |              Type              | Modifiers
--------------+--------------------------------+-----------
 lineitem_key | bigint                         | not null
 time         | timestamp(6) without time zone | not null
 description  | text                           | not null
 barcode      | text                           | not null
 amount       | bigint                         | not null
Indexes:
    "l1_amount_index" btree (amount, lineitem_key)
Inherits: l

metadb=> \d L2
                     Table "test2.l2"
    Column    |              Type              | Modifiers
--------------+--------------------------------+-----------
 lineitem_key | bigint                         | not null
 time         | timestamp(6) without time zone | not null
 description  | text                           | not null
 barcode      | text                           | not null
 amount       | bigint                         | not null
Indexes:
    "l2_amount_index" btree (amount, lineitem_key)
Inherits: l

metadb=> explain select * from l order by amount,lineitem_key limit 10;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Limit  (cost=22207.70..22207.72 rows=10 width=88)
   ->  Sort  (cost=22207.70..23548.09 rows=536156 width=88)
         Sort Key: test2.l.amount, test2.l.lineitem_key
         ->  Result  (cost=0.00..10621.56 rows=536156 width=88)
               ->  Append  (cost=0.00..10621.56 rows=536156 width=88)
                     ->  Seq Scan on l  (cost=0.00..16.90 rows=690 width=88)
                     ->  Seq Scan on l1 l  (cost=0.00..4951.00 
rows=250000 width=49)
                     ->  Seq Scan on l2 l  (cost=0.00..5653.66 
rows=285466 width=49)
(8 rows)


NB. Just addressing one of the inherited tables works fine.

metadb=> explain select * from l1 order by amount,lineitem_key limit 10;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.74 rows=10 width=49)
   ->  Index Scan using l1_amount_index on l1  (cost=0.00..18554.20 
rows=250000 width=49)
(2 rows)
 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>