All, * Josh Berkus (josh@agliodbs.com) wrote: > > The plain non-VALUES list form is also significantly faster than it > > was, but I think it will only result in a bitmap indexscan plan type. > > Yeah, even bitmapscans break down at 1000 values ...
In a similar vein, perhaps 8.2 fixes this but I don't recall seeing anything where it would... Working on 8.1 I've recently been annoyed at the need to translate a sub-select inside an IN () clause into a fixed list of contents (the results of the sub-select, exactly) in order to get better performance. If the results of a sub-select are very likely to be less than 1000 (or what have you) is there a reason not to translate that sub-select into a VALUES list or IN (constants) set (ie: a nest-loop or a bitmap indexscan)? This particular case was involving 9 values from a table which only had around 250 rows total being used to find a set of records in a much, much bigger table (60M or so, iirc). I dislike having to hard-code those values in the scripts I'm writing, or hack it up to implement getting the list and then using it as a constant. A similar case I've seen is that when using a sub-select or similar instead of a list of constants the 'One-Time Filter: false' doesn't appear to ever be able to happen. I might have overlooked something else which doesn't something similar, but if not this ends up making a query *much* more expensive when alot of disjoint tables are involved, most of which don't need to be considered since they're not in the constants list. Thanks, Stephen
signature.asc
Description: Digital signature