pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mario Ivankovits <mario@ops.co.at>
To: pgsql-performance@postgresql.org
Subject: index not used if using IN or OR
Date: Thu, 04 Nov 2004 08:55:50 +0100
Message-ID: <4189E086.5060400@ops.co.at> (raw)

Hello !

Sorry if this has been discussed before, it is just hard to find in the 
archives using the words "or" or "in" :-o

I use postgres-8.0 beta4 for windows.
I broke down my problem to a very simple table - two columns 
"primary_key" and "secondary_key". Creates and Insert you will find below.

If I query the _empty_ freshly created table I get the following explain 
result:

select * from tt where seckey = 1;
Index Scan using seckey_key on tt  (cost=0.00..17.07 rows=5 width=12)
  Index Cond: (seckey = 1)

If I use "OR" (or IN) things get worse:

select * from tt where seckey = 1 or seckey = 2
Seq Scan on tt  (cost=0.00..0.00 rows=1 width=12)
  Filter: ((seckey = 1) OR (seckey = 2))

Note the "Seq Scan" instead of using the index.

After populating the table with 8920 records and "analyze" the scenario 
gets even worser:

select * from tt where seckey = 1;
Seq Scan on tt  (cost=0.00..168.50 rows=1669 width=12) (actual 
time=0.000..15.000 rows=1784 loops=1)
  Filter: (seckey = 1)
Total runtime: 31.000 ms

Now also this simple query uses a "Seq Scan".

Now the questions are:
a) Why is the index not used if I use "OR" or "IN"
b) Why is the index not used after "analyze" ?

Any help is very appreciated!

Thanks,
Mario


// The table and data

CREATE TABLE tt (
  pkey int4 NOT NULL DEFAULT nextval('public."tt_PKEY_seq"'::text),
  seckey int8,
  CONSTRAINT pkey_key PRIMARY KEY (pkey)
)
WITHOUT OIDS;

CREATE INDEX seckey_key  ON tt  USING btree  (seckey);

// inserted many-many times
insert into tt values (default, 1);
insert into tt values (default, 2);
insert into tt values (default, 3);
insert into tt values (default, 4);
insert into tt values (default, 5);




view thread (3+ messages)  latest in thread

Message-ID: <4189E086.5060400@ops.co.at>
Permalink:  ../4189E086.5060400@ops.co.at/
Also on:    postgresql.org/message-id/4189E086.5060400@ops.co.at

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mario@ops.co.at
  Subject: Re: index not used if using IN or OR
  In-Reply-To: <4189E086.5060400@ops.co.at>

* 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