I'm having a problem with some of my tables and I'm not sure if postgres' behaviour is maybe even a bug. I'm (still) using 8.0rc5 at present.
I have a table that contains among other columns one of the sort: purge_date timestamp
most records will have this field set to NULL, at present all of them really. the table has about 100k row right now. in regular intervals I'm doing some cleanup on this table using a query like:
delete from mytable where purge_date is not null and purge_date < current_date
And I have created these btree indexes: create index on mytable (purge_date); create index on mytable (purge_date) where purge_date is not null;
my problem is that the planner always chooses a seq scan over an index scan. only when I set enable_seqscan to false does it use an index scan. The costs of both plans are extremely different, with the index scan being 5-10 times more expensive than the seq scan, which is obviously not true given that all rows have this column set to NULL.
I wondered why the planner was making such bad assumptions about the number of rows to find and had a look at pg_stats. and there was the surprise:
there is no entry in pg_stats for that column at all!! I can only suspect that this has to do with the column being all null. I tried to change a few records to a not-null value, but re-ANALYZE didn't catch them apparently.
Is this desired behaviour for analyze? Can I change it somehow? If not, is there a better way to accomplish what I'm trying? I'm not to keen on disabling seqscan for that query explicitly. It's a simple enough query and the planner should be able to find the right plan without help - and I'm sure it would if it had stats about it.
Any help appreciated.
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]