Re: Planner chooses multi-column index in 9.2 when maybe it should not - Mailing list pgsql-general

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@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8