Re: Planner chooses multi-column index in 9.2 when maybe it should not

Found a good demonstration of the problem. Here's explain analyze of a
query on 9.2 with enable_indexonlyscan = off; This produces the exact same
plan as 8.3. The tables in question have been analyzed. Changing
random_page_cost has no effect. The main foobar table has 17M rows.
I did multiple runs of both to eliminate any caching effects.

foobar.id is VARCHAR(16)
foobar.status is VARCHAR(32)

Indexes:
"foobar_pkey" PRIMARY KEY, btree (id) CLUSTER
"foobar_status" UNIQUE, btree (status, id)

(8.3 and up, plus 9.2 with index scan disabled)
GroupAggregate (C=30389..1754503 R=1 W=22) (AT=0.3..0.3 R=1 L=1)
-> Nested Loop Left Join (C=30389..1754147 R=23751 W=22) (AT=0.1..0.2 R=7 L=1)
-> Nested Loop Left Join (C=30389..835374 R=8980 W=16) (AT=0.1.0.1 R=1 L=1)
-> Index Scan using foobar_pkey on foobar m (C=0..13 R=1 W=8) (AT=0.03..0.03 rows=1 L=1)
Index Cond: ((id) = '17464097')
Filter: ((id) !~~ '%.%')
-> Bitmap Heap Scan on foobar o (C=30389..835271 R=8980 W=8) (AT=0.06..0.07 R=1 L=1)
Recheck Cond: (((id) >= (m.id)) AND ((id) <= ((m.id) || '.999999')))
Filter: (((status) <> ALL ('{panda,penguin}'[])) \
AND ((id) ~ (('^' || (m.id)) || '(\.[0-9.]+)?$')))
-> Bitmap Index Scan on foobar_pkey (C=0..30386 R=1888670 W=0) (AT=0.02..0.02 R=1 L=1)
Index Cond: (((id) >= (m.id)) AND ((id) <= ((m.id) || '.999999')))
-> Index Scan using baz_id on baz (C=0..101 R=40 W=22) (AT=0.03..0.06 R=7 L=1)
Index Cond: ((o.id) = (id))
Filter: (price <> 0::numeric)
Rows Removed by Filter: 3
Total runtime: 0.459 ms

Now, if we turn on index only scans, we get a terrible runtime:

GroupAggregate (C=0.00..1314945 R=1 W=22) (AT=34502..34502 R=1 L=1)
-> Nested Loop Left Join (C=0.00..1314589 R=23751 W=22) (AT=31934..34502 R=7 L=1)
-> Nested Loop Left Join (C=0.00..395816 R=8980 W=16) (AT=31934..34502 R=1 L=1)
-> Index Only Scan using foobar_pkey on foobar m (C=0.00..13.81 R=1 W=8) (AT=0.029..0.034 R=1 L=1)
Index Cond: (id = '17464097')
Filter: ((id) !~~ '%.%')
Heap Fetches: 0
-> Index Only Scan using foobar_status on foobar o (C=0.00..395713 R=8980 W=8) (AT=31934..34502 R=1 L=1)
Index Cond: ((id >= (m.id)) AND (id <= ((m.id) || '.999999')))
Filter: (((status) <> ALL ('{panda,penguin}'[])) \
AND ((id) ~ (('^' || (m.id)) || '(\.[0-9.]+)?$')))
Heap Fetches: 0
-> Index Scan using baz_id on baz (C=0..101 R=40 W=22) (AT=0.1..0.2 R=7 L=1)
Index Cond: ((o.id) = (id))
Filter: (price <> 0::numeric)
Rows Removed by Filter: 3

Total runtime: 34502.670 ms

Yeah....34 seconds versus near-instant. The first index-only scan does great,
but that second one - ouch - even with no heap fetches at all!

--
Greg Sabino Mullane greg(at)endpoint(dot)com
End Point Corporation
PGP Key: 0x14964AC8