From: Richard Huxton <dev@archonet.com>
To: Mario Ivankovits <mario@ops.co.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: index not used if using IN or OR
Date: Thu, 04 Nov 2004 08:32:08 +0000
Message-ID: <4189E908.2000104@archonet.com> (raw)
In-Reply-To: <4189E086.5060400@ops.co.at>
References: <4189E086.5060400@ops.co.at>
Mario Ivankovits wrote:
> 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.
But as you said, your table is *empty* - why would an index be faster?
Try running EXPLAIN ANALYSE on these queries and look at the actual times.
> 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".
Well, it thinks it's going to be returning 1669 rows. If that's roughly
right, then scanning the table probably is faster.
Run the queries again with EXPLAIN ANALYSE. Also try issuing
set enable_seqscan=false;
This will force the planner to use any indexes it finds. Compare the
times with and without, and don't forget to account for the effects of
caching.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, mario@ops.co.at
Subject: Re: index not used if using IN or OR
In-Reply-To: <4189E908.2000104@archonet.com>
* 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