From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to use index in WHERE int = float |
Date: | 2008-11-05 21:21:33 |
Message-ID: | dcc563d10811051321s117c9262tb17eaaa3c2eea0d5@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Nov 5, 2008 at 11:52 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
> 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 ?
Stop trying to compare exact and inexact types?
You do realize that a float is not an exact number. What you and I
see as 1228137 might really be, internally, 1228136.9999999999999999 ?
So it won't get an exact match. What's wrong with trying to match to
an exact number instead? I have a feeling you've given us a
simplified example of your problem. It might help to see the real
problem.
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Long | 2008-11-05 21:40:19 | Re: Debugging infrequent pegged out CPU usage |
Previous Message | Tony Fernandez | 2008-11-05 21:19:28 | Upgrading Postgres question |