pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: George Young <gry@ll.mit.edu>
To: pgsql-general@postgresql.org
To: pgsql-sql@postgresql.org
Subject: bad select performance for where (x=1 or x=3)
Date: Tue, 20 Jul 1999 10:02:39 -0400 (EDT)
Message-ID: <199907201401.KAA03008@ll.mit.edu> (raw)

[PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66]

I have a 'select' including 'where (x=1 or x=3)' that takes 16 times as long
as with just 'where x=1'.  Here's a (somewhat simplified) example:

table opset_steps
      (name text, id int2, ver int2) [1400 rows]
      non-unique index is on (id, ver)

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

table runs
      (run_name text, run_id int2, status int2) [900 rows]
      pkey is run_name, second index(run_id, status)      

I have vacuum analyzed all relevant tables.

This query takes 16 seconds (without the explain of course):
  explain select os.name,r.run_name,ro.status from opset_steps os,runs r,run_opsets ro where (ro.status=1 or ro.status=3) and ro.opset_id=os.opset_id and ro.run_id=r.run_id and ro.opset_ver=os.opset_ver and r.status=1;

Hash Join  (cost=1793.58 rows=14560 width=38)
  ->  Hash Join  (cost=1266.98 rows=14086 width=24)
        ->  Seq Scan on run_opsets ro  (cost=685.51 rows=13903 width=8)
        ->  Hash  (cost=70.84 rows=1389 width=16)
              ->  Seq Scan on opset_steps os  (cost=70.84 rows=1389 width=16)
  ->  Hash  (cost=47.43 rows=374 width=14)
        ->  Seq Scan on runs r  (cost=47.43 rows=374 width=14)


This query takes just under one second:[diff is status=1 instead of (1 or 3)]
  explain select os.name,r.run_name,ro.status from opset_steps os,runs r,run_opsets ro where ro.status=1 and ro.opset_id=os.opset_id and ro.run_id=r.run_id and ro.opset_ver=os.opset_ver and r.status=1;

Hash Join  (cost=1359.57 rows=7719 width=38)
  ->  Hash Join  (cost=1051.39 rows=7467 width=24)
        ->  Seq Scan on run_opsets ro  (cost=685.51 rows=7370 width=8)
        ->  Hash  (cost=70.84 rows=1389 width=16)
              ->  Seq Scan on opset_steps os  (cost=70.84 rows=1389 width=16)
  ->  Hash  (cost=47.43 rows=374 width=14)
        ->  Seq Scan on runs r  (cost=47.43 rows=374 width=14)


Why should it take over 16 times as long for (status=1 or 3) as for status=1?
I have indexes on exactly all the fields used in the where clause.
I tried "status in (1,3)" with no improvement.


George Young,  Rm. L-204		gry@ll.mit.edu
MIT Lincoln Laboratory
244 Wood St.
Lexington, Massachusetts  02420-9108	(781) 981-2756



view thread (2+ messages)  latest in thread

Message-ID: <199907201401.KAA03008@ll.mit.edu>
Permalink:  ../199907201401.KAA03008@ll.mit.edu/
Also on:    postgresql.org/message-id/199907201401.KAA03008@ll.mit.edu

 · 

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: gry@ll.mit.edu, pgsql-general@postgresql.org
  Subject: Re: bad select performance for where (x=1 or x=3)
  In-Reply-To: <199907201401.KAA03008@ll.mit.edu>
  References: <199907201401.KAA03008@ll.mit.edu>

* 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