pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgres does not utilize indexes. Why?
3+ messages / 3 participants
[nested] [flat]

* Postgres does not utilize indexes. Why?
@ 2004-08-17 15:35  Artimenko Igor <igorart7@yahoo.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Artimenko Igor @ 2004-08-17 15:35 UTC (permalink / raw)
  To: pgsql-performance

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 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Postgres does not utilize indexes. Why?
@ 2004-08-17 15:45  Michal Taborsky <michal@taborsky.cz>
  parent: Artimenko Igor <igorart7@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Michal Taborsky @ 2004-08-17 15:45 UTC (permalink / raw)
  To: Artimenko Igor <igorart7@yahoo.com>; +Cc: pgsql-performance

Artimenko Igor wrote:
>   id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,

ID column is bigint, but '50000' is int, therefore the index does not 
match. You need to cast your clause like this:

select id from test where id = 50000::int8

Also, issue VACUUM ANALYZE, so Postgres knows about the structure of the 
data.

-- 
Michal Taborsky
http://www.taborsky.cz




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Postgres does not utilize indexes. Why?
@ 2004-08-17 18:33  Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  parent: Artimenko Igor <igorart7@yahoo.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Pierre-Frédéric Caillaud @ 2004-08-17 18:33 UTC (permalink / raw)
  To: Artimenko Igor <igorart7@yahoo.com>; pgsql-performance

> test where id = 50000;” Few times I added 100,000 records, applied

	cast the 50000 to int8 and it will use the index



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2004-08-17 18:33 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-17 15:35 Postgres does not utilize indexes. Why? Artimenko Igor <igorart7@yahoo.com>
2004-08-17 15:45 ` Michal Taborsky <michal@taborsky.cz>
2004-08-17 18:33 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>

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