Hi everybody!
I can’t make use of indexes even I tried the same test by changing different settings
in
postgres.conf like geqo to off/on & geqo related parameters, enable_seqscan off/on &
so on. Result
is the same.
Here is test itself:
I’ve created simplest table test and executed the same statement “explain analyze
select id from
test where id = 50000;” Few times I added 100,000 records, applied vacuum full; and
issued above
explain command.
Postgres uses sequential scan instead of index one.
Of cause Time to execute the same statement constantly grows. In my mind index should
not allow
time to grow so much.
Why Postgres does not utilizes primary unique index?
What I’m missing? It continue growing even there are 1,200,000 records. It should at
least start
using index at some point.
Details are below:
100,000 records:
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..2427.00 rows=2 width=8) (actual time=99.626..199.835
rows=1
loops=1)
Filter: (id = 50000)
Total runtime: 199.990 ms
200,000 records:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..4853.00 rows=2 width=8) (actual time=100.389..402.770
rows=1
loops=1)
Filter: (id = 50000)
Total runtime: 402.926 ms
300,000 records:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..7280.00 rows=1 width=8) (actual time=100.563..616.064
rows=1
loops=1)
Filter: (id = 50000)
Total runtime: 616.224 ms
(3 rows)
I've created test table by script:
CREATE TABLE test
(
id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,
description char(50),
CONSTRAINT users_pkey PRIMARY KEY (id)
);
CREATE SEQUENCE next_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 10000000000
START 1
CACHE 5
CYCLE;
I use postgres 7.4.2
=====
Thanks a lot
Igor Artimenko
I specialize in
Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data
Modeling
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
