> 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.
>
>> metadb=> explain select * from l order by amount,lineitem_key limit 10;
>> QUERY PLAN
>> -> 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)
>
>> NB. Just addressing one of the inherited tables works fine.
>>
>> metadb=> explain select * from l1 order by amount,lineitem_key limit 10;
>
> Well, you don't have an index it can use to find the smallest
> (amount,lineitem) across all of lX. If PG was smart enough to figure
> out that it only needed to check l1, then you do. Unfortunately it isn't.
>
You're right, it can't determine which of the partitions will have the
smallest value, but what it could do is pull the smallest value from
each and compare. In the absence of the LIMIT there wouldn't be much
which could be done, but the the LIMIT means it only actually needs to
pull 10 rows from each partition. An alternative way of doing this would be:
(SELECT * FROM L1 ORDER BY amount,lineitem_key LIMIT 10)
UNION
(SELECT * FROM L2 ORDER BY amount,lineitem_key LIMIT 10)
ORDER BY amount,lineitem_key LIMIT 10;
Unfortunately this means one can't just address the parent table, but it
does essentially what I'd hoped postgres would do for me :) It would be
quite a long query if there were 100 partitions!
> If you add the constraint you use to partition by, does that help you?
>
I tried to strip the example down to its bare essentials but in this
case I would be partitioning by lineitem_key and would obviously index
and add a CONSTRAINT on that as well. I don't think it would help
though, the query needs to merge from all tables.
Thanks for the response,Stuart