pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: George Young <gry@ll.mit.edu>
Cc: pgsql-general@postgreSQL.org
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] bad select performance for where (x=1 or x=3)
Date: Tue, 20 Jul 1999 10:53:39 -0400
Message-ID: <29637.932482419@sss.pgh.pa.us> (raw)
In-Reply-To: <199907201401.KAA03008@ll.mit.edu>

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



view thread (2+ messages)

Message-ID: <29637.932482419@sss.pgh.pa.us>
Permalink:  ../29637.932482419@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/29637.932482419@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, gry@ll.mit.edu, pgsql-general@postgreSQL.org
  Subject: Re: [SQL] bad select performance for where (x=1 or x=3)
  In-Reply-To: <29637.932482419@sss.pgh.pa.us>
  References: <199907201401.KAA03008@ll.mit.edu> <29637.932482419@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox