Joe Lester <joe_lester@xxxxxxxxxxxxxx> writes: > SELECT count(*) FROM purchase_order_items WHERE expected_quantity > '0' > Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual > time=2205.688..2205.724 rows=1 loops=1) > -> Seq Scan on purchase_order_items (cost=0.00..21978.08 > rows=286882 width=0) (actual time=0.535..2184.405 rows=7458 loops=1) > Filter: (expected_quantity > 0) > Total runtime: 2207.203 ms Why is the expected row count so far off --- have you analyzed the table lately? For such a simple WHERE condition the estimate should be pretty accurate, if the stats are sufficient. If this table is very large you might need to increase the statistics targets, but more likely you just haven't got up-to-date stats at all. The planner *never* "ignores" an index. It may deliberately decide not to use it, if it thinks the seqscan plan will be faster, as it does in this case --- note the much higher cost estimate for the indexscan: > SET ENABLE_SEQSCAN TO OFF; > EXPLAIN ANALYZE SELECT count(*) FROM purchase_order_items WHERE > expected_quantity > '0' > Aggregate (cost=1050659.46..1050659.46 rows=1 width=0) (actual > time=137.393..137.441 rows=1 loops=1) > -> Index Scan using purchase_order_items_expected_quantity_idx on > purchase_order_items (cost=0.00..1049942.25 rows=286882 width=0) > (actual time=0.756..119.990 rows=7458 loops=1) > Index Cond: (expected_quantity > 0) > Total runtime: 139.185 ms The reason the cost estimate is out of line with reality is mainly that the rows estimate is out of line with reality. There may be some index order correlation it's not aware of too. BTW you might want to think about updating to PG 8.1. Its "bitmap" index scans are much better suited for queries that are using a relatively unselective index condition. regards, tom lane
