Oh, seems the table partitioning I set up with so much effort,
isn't so powerful as I hoped to be:
I do a select on the main table, which postgres correctly restricts
to one of the inherited table because of its check constraint.
Still the order by clause does not make use of the index that I
configured for the inherited table.
Is there anything I can do to make this work?
I looked into creating another rule that replaces the table name on selects
but it seems that the parent table must be a view in that case, which
completely disables constraint exclusion, right?
Clueless. Please help if you can...
------------------------------------------------------------------------
Here is the plan if I query the inherited table directly
(which is not really possible to do with that stupid, inflexible
hibernate framework):
explain select this_.id as id1_0_, <more columns>
from call_sources_10554 this_
where this_.cdr_id = 10554
order by this_.id asc
limit 100;
Limit (cost=0.00..4.09 rows=100 width=8907)
-> Index Scan using call_source_10554_id_index on call_sources_10554 this_ (cost=0.00..23510.68 rows=575532
width=8907)
Filter: (cdr_id = 10554)
------------------------------------------------------------------------
However the same query executed in the parent table ("... from call_sources ...") gives:
Limit (cost=23362879.77..23362880.02 rows=100 width=13436)
-> Sort (cost=23362879.77..23364318.60 rows=575533 width=13436)
Sort Key: this_.id
-> Result (cost=0.00..20741.97 rows=575533 width=13436)
-> Append (cost=0.00..20741.97 rows=575533 width=13436)
-> Index Scan using call_source_cdr_index on call_sources this_ (cost=0.00..4.82 rows=1
width=13436)
Index Cond: (cdr_id = 10554)
-> Seq Scan on call_sources_10554 this_ (cost=0.00..20737.15 rows=575532 width=8907)
Filter: (cdr_id = 10554)