Re: [SQL] bad select performance for where (x=1 or x=3)

George Young <gry@ll.mit.edu> writes:
> table run_opsets
>       (status int2, id int2, ver int2, run_id int2, seq int2) [17000 rows]
>       pkey is (id, seq), second index on(status, id, ver, run_id)

>       select count(*) from run_opsets where status=1; --> 187
>       select count(*) from run_opsets where status=3; --> 10564

> Why should it take over 16 times as long for (status=1 or 3) as for status=1?

Offhand it looks like the former would produce 57 times as many possible
rows from the run_opsets table as the latter (187+10564 vs 187), which
the system would then have to try to match against the other tables.
You didn't say how many tuples actually get returned, but certainly the
number of iterations through each of the join loops is likely to be much
higher.  I'm surprised the cost differential isn't more than 16:1.

A more interesting question might be "why doesn't the system's cost
estimator realize that the second case will be much cheaper?"  The
answer to that is that VACUUM ANALYZE doesn't keep sufficiently detailed
statistics to let it realize that there are far more x=3 than x=1 rows.

            regards, tom lane