How to use index in WHERE int = float

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: How to use index in WHERE int = float
Date: 2008-11-05 18:52:50
Message-ID: gesq8j$1poc$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have table with index

CREATE TABLE firma2.dok(
...
dokumnr serial NOT NULL,
...
CONSTRAINT dok_pkey PRIMARY KEY (dokumnr),
...
);

CREATE INDEX dok_dokumnr_idx
ON firma2.dok
USING btree
(dokumnr);

I ran analyze command on it.

explain analyze select * from firma2.dok where dokumnr='1228137'::float8

"Seq Scan on dok (cost=0.00..187766.23 rows=6255 width=1145) (actual
time=43168.460..43176.063 rows=1 loops=1)"
" Filter: ((dokumnr)::double precision = 1228137::double precision)"
"Total runtime: 43176.375 ms"

shows that index is not used and thus query takes very long time.

How to force PostgreSql to speed up without changing query ?

"PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0,
pie-8.7.9)"

Andrus.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2008-11-05 19:09:54 Re: Speeding up query
Previous Message Alvaro Herrera 2008-11-05 18:52:06 Re: worker took too long to start; cancelled