pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex not used if using IN or OR
3+ messages / 3 participants
[nested] [flat]
* index not used if using IN or OR
@ 2004-11-04 07:55 Mario Ivankovits <mario@ops.co.at>
2004-11-04 08:32 ` Re: index not used if using IN or OR Richard Huxton <dev@archonet.com>
2004-11-04 14:52 ` Re: index not used if using IN or OR Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Mario Ivankovits @ 2004-11-04 07:55 UTC (permalink / raw)
To: pgsql-performance
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);
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index not used if using IN or OR
2004-11-04 07:55 index not used if using IN or OR Mario Ivankovits <mario@ops.co.at>
@ 2004-11-04 08:32 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2004-11-04 08:32 UTC (permalink / raw)
To: Mario Ivankovits <mario@ops.co.at>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: index not used if using IN or OR
2004-11-04 07:55 index not used if using IN or OR Mario Ivankovits <mario@ops.co.at>
@ 2004-11-04 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-11-04 14:52 UTC (permalink / raw)
To: Mario Ivankovits <mario@ops.co.at>; +Cc: pgsql-performance
Mario Ivankovits <mario@ops.co.at> writes:
> 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".
Which is exactly what it *should* do, considering that it is selecting
1784 out of 8920 records. Indexscans only win for small selectivities
--- the rule of thumb is that retrieving more than about 1% of the
records should use a seqscan.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-11-04 14:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-04 07:55 index not used if using IN or OR Mario Ivankovits <mario@ops.co.at>
2004-11-04 08:32 ` Richard Huxton <dev@archonet.com>
2004-11-04 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
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