Hi all!
I have set up a couple of log tables in our database to use table
partitioning. So far it looks to be working pretty well, but I ran into
a query plan issue that doesn't make sense to me at all.
We have a table "omslog", and a set of partition tables
"omslog_part_########", where the "#..." stuff is a serial number.
There is a rule that redirects inserts into omslog, to the most recent
partition table. Pretty simple, and it works as advertised.
If I select a min() or max() aggregate against an indexed field in
omslog, where as before it would use the index, now it does not.
However, the index will be used if I execute the same aggregate against
one of the partition tables. The difference is demonstrated in the
explains below.
Doing a sequential scan on the base table makes absolutely no sense
whatsoever by any stretch of the imagination; as you can see, there are
about 9 million rows there, and the planner knows it (frequent
analyze). What would cause the planner to be so silly in this instance?
This is on PG 8.1.4. Would this happen to be something that an
upgrade might fix?
Thanks for any insight,
-Glen
database=# explain select min(sub_system) from omslog_part_00000002;
Result (cost=0.19..0.20 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.19 rows=1 width=14)
-> Index Scan using idx_omslog_part_00000002_subsys on
omslog_part_00000002 (cost=0.00..24212.57 rows=129781 width=14)
Filter: ((sub_system)::text IS NOT NULL)
database=# explain select min(sub_system) from omslog;
Aggregate (cost=539575.39..539575.40 rows=1 width=14)
-> Append (cost=0.00..516884.11 rows=9076511 width=14)
-> Seq Scan on omslog (cost=0.00..509342.09 rows=8946709
width=14)
-> Seq Scan on omslog_part_00000002 omslog
(cost=0.00..7542.02 rows=129802 width=14)