Greg Sabino Mullane <greg@xxxxxxxxxxxx> writes: > We are seeing a performance regression when moving to 9.2. There is a > complex query that is doing a self-join, but long story short, it > is choosing to use a multi-column index when it really ought not to be. > I was not able to develop a standalone test case without resorting > to changing enable_seqscan, but this does show the difference: > CREATE TABLE gregtest (a TEXT PRIMARY KEY, b TEXT); > CREATE INDEX gregtest_i ON gregtest(b,a); > SET enable_seqscan = off; > EXPLAIN SELECT 1 FROM gregtest WHERE a <> 'panda' AND b <> 'yak'; > On versions 8.2, 8.3, 8.4, 9.0, and 9.1, this gives: > Seq Scan on gregtest (cost=10000000000.00..10000000022.90 rows=855 width=0) > Filter: ((a <> 'panda'::text) AND (b <> 'yak'::text)) > Which makes sense - I would imagine that b = 'yak' would use the index, > but the negation means the index is not very useful? > However, on 9.2, this gives: > Bitmap Heap Scan on gregtest (cost=8.76..31.66 rows=855 width=0) > Filter: ((a <> 'panda'::text) AND (b <> 'yak'::text)) > -> Bitmap Index Scan on gregtest_i (cost=0.00..8.55 rows=860 width=0) The above doesn't seem like a regression to me. You told it not to use a seqscan, and it didn't. (The reason it now considers the index is that an index-only scan is possible; before 9.2 there was no reason to consider an indexscan at all given this query, so you got the seqscan despite the attempted disable.) > The above was tested on stock versions of Postgres, with no changes > made to postgresql.conf. In the actual query, the result is something like > this on 9.2 (columns explained below): > Nested Loop (cost=0.00..6050226723847.12 rows=282638194054762 width=8) > Join Filter: ((o.foo)::text <> ((m.foo)::text || 'panda'::text)) > -> Index Only Scan using index_i on foobar m (cost=0.00..889187.83 rows=16998032 width=8) > -> Materialize (cost=0.00..1079773.42 rows=16627702 width=8) > -> Index Only Scan using index_i on foobar o (cost=0.00..931682.91 rows=16627702 width=8) > Filter: ((status)::text <> 'split'::text) It's not obvious that this is a worse plan than a seqscan --- the index-only scans will only have to read the index not the heap, at least if the heap is all-visible. If it's coming out slower, then that's a question of whether the cost estimates match reality. I'd wonder how many heap fetches occur anyway, and also whether you've tweaked the planner cost parameters. You should be able to force it back to the seqscan based plan by turning off enable_indexscan or enable_indexonlyscan. It would be useful to see EXPLAIN ANALYZE (not just EXPLAIN) results for both this plan and the seqscan plan in 9.2. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
