From: | Jason Earl <jason(dot)earl(at)simplot(dot)com> |
---|---|
To: | "Alaric B(dot) Snell" <abs(at)frontwire(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Query planner isn't using my indices |
Date: | 2002-01-09 00:41:33 |
Message-ID: | 87hepwicea.fsf@npa01zz001.simplot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
If you cast the 1 to type bigint then PostgreSQL will use the index,
otherwise it won't. You can do one of four things:
1) An explicit cast (btw what's the SQL92 way of doing this):
SELECT * FROM stakeholder WHERE id = 1::bigint;
2) Put the constant in "'" and let PostgreSQL work out what to do:
SELECT * FROM stakeholder WHERE id = '1';
3) Turn off sequential scans (see the manual).
4) Live with sequential scans :).
Jason
"Alaric B. Snell" <abs(at)frontwire(dot)com> writes:
> To cut a long story short, my largish development database was running the
> query I was tinkering with very slowly.
>
> Looking a little deeper, I found that it was always doing a full table
> scan.
>
> Which is odd, seeing as we're selecting on a uniquely indexed field...
>
> frontwire=# \d stakeholder_pk
> Index "stakeholder_pk"
> Attribute | Type
> -----------+--------
> id | bigint
> unique btree
>
> frontwire=# explain select * from stakeholder where id = 1;
> NOTICE: QUERY PLAN:
>
> Seq Scan on stakeholder (cost=0.00..602.81 rows=1 width=336)
>
> EXPLAIN
> frontwire=# select count(*) from stakeholder;
> count
> -------
> 9170
> (1 row)
>
> ...why is this happening? It... shouldn't!
>
> ABS
>
> --
> Alaric B. Snell, Developer
> abs(at)frontwire(dot)com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Welter | 2002-01-09 02:34:07 | Very large database |
Previous Message | Doug McNaught | 2002-01-09 00:04:32 | Re: [SQL] Need help |