Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2] (may be forged)) by hub.org (8.9.3/8.9.3) with ESMTP id KAA08764; Tue, 20 Jul 1999 10:54:12 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA29639; Tue, 20 Jul 1999 10:53:39 -0400 (EDT) To: George Young cc: pgsql-general@postgreSQL.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] bad select performance for where (x=1 or x=3) In-reply-to: Your message of Tue, 20 Jul 1999 10:02:39 -0400 (EDT) <199907201401.KAA03008@ll.mit.edu> Date: Tue, 20 Jul 1999 10:53:39 -0400 Message-ID: <29637.932482419@sss.pgh.pa.us> From: Tom Lane George Young 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