Re: IN(subselect returning few values ...)

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > 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.
>
> Better performance than what? Ever since 7.4 we've converted small IN
> sub-selects into plans along the lines of

Specifically what I had been looking for a change from a HASH IN
plan w/ seq-scan on the big table to a bitmap index scan or a nested
loop index lookup (as you have below). With the IN(constants) I had
been getting a bitmap-index scan. I looked a bit closer though and
discovered it was thinking there would be 300+ rows returned from the
query (which would have resulted in a very much larger number of rows
being returned from the large table) instead of just 9, so I ran
analyze on the table and that seemed to fix it up (changed to a nested
loop w/ an index scan, which works nicely).

I've got autovacuum running though and that table hasn't changed in ages
so I'm a bit confused how the stats for it were so far off. I didn't
expect to have an analyze problem on a database that has autovacuum
running on a table that hasn't changed in a very long time. Wish I knew
how it'd been missed. :/ I'm running a database-wide analyze, though
that'll probably take a while considering it about 300G. Makes me
wonder if autovacuum needs to periodically run a check of tables which
havn't been seen to have changed but may have in important ways which
were somehow missed, not unlike how my SAN and RAID systems run monthly
consistancy checks...

Sorry about the noise. :/

Thanks,

Stephen