Re: 8.2rc1 (much) slower than 8.2dev?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Arjen van der Meijden <acmmailing(at)tweakers(dot)net>
Cc: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>, pgsql-performance(at)postgresql(dot)org
Subject: Re: 8.2rc1 (much) slower than 8.2dev?
Date: 2006-12-07 15:38:10
Message-ID: 15452.1165505890@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-patches pgsql-performance

Arjen van der Meijden <acmmailing(at)tweakers(dot)net> writes:
> I've been mailing off-list with Tom and we found at least one
> query that in some circumstances takes a lot more time than it should,
> due to it mistakenly chosing to do a bitmap index scan rather than a
> normal index scan.

Just to clue folks in: the problem queries seem to be cases like

WHERE col1 = 'const'
AND col2 = othertab.colx
AND col3 IN (several hundred integers)

where the table has an index on (col1,col2,col3). 8.2 is generating
a plan involving a nestloop with inner bitmap indexscan on this index,
and using all three of these WHERE clauses with the index. The ability
to use an IN clause (ie, ScalarArrayOpExpr) in an index condition is
new in 8.2, and we seem to have a few bugs left in the cost estimation
for it. The problem is that a ScalarArrayOpExpr effectively causes a
BitmapOr across N index scans using each of the array elements as an
individual scan qualifier. So the above amounts to several hundred
index probes for each outer row. In Arjen's scenario it seems that
the first two index columns are already pretty selective, and it comes
out a lot faster if you just do one indexscan using the first two
columns and then apply the IN-condition as a filter to the relatively
small number of rows you get that way.

What's not clear to me yet is why the 8.2dev code didn't fall into this
same trap, because the ScalarArrayOpExpr indexing code was already there
on 3-June. But we didn't and still don't have any code that considers
the possibility that a potential indexqual condition should be
deliberately *not* used with the index.

regards, tom lane

In response to

Browse pgsql-patches by date

  From Date Subject
Next Message Kevin Grittner 2006-12-07 16:03:05 Re: Load distributed checkpoint
Previous Message Javier Molina 2006-12-07 13:39:39 Re: 8.2.0 pdf

Browse pgsql-performance by date

  From Date Subject
Next Message Shane Ambler 2006-12-07 15:43:30 Re: Areca 1260 Performance
Previous Message Marcos Borges 2006-12-07 13:19:15 SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can helpe me.