Re: 335 times faster (!)

> ------- Ursprungligt meddelande -------
>
> Från: Nigel J. Andrews <nandrews(at)investsystems(dot)co(dot)uk>
> Datum: Mon, 3 Feb 2003 17:59:12 +0000 (GMT)
>
>
>Could it be that you've got a reasonably powerful machine and that your table
>isn't very wide?

Machine: Intel P3 650 laptop /w 256 RAM
Table: 10 columns

>Are you sure your search using the primary key was actually using the primary
>key index, i.e. did you just do:
>
> SELECT * FROM mytable WHERE pkcol = 45
>

Yep:
select * from enheter where enhetsid = xxxxxxxxx;

>Also, did you perhaps do your search on the text type column just after doing
>the first SELECT? You might find there's some caching issue.

I tested this (after you pointed it out) by alterating between the same two queries back and forth, but they still differ by the same amount.

>
>Not sure about anyone else but I think we'd want to see the plans used for your
>queries, in addition to the queries, before accepting this.
>

explain select * from enheter where enhetsid = 200178146;
QUERY PLAN
------------------------------------------------------------
Seq Scan on enheter (cost=0.00..15678.50 rows=1 width=91) Filter: (enhetsid = 200178146)
(2 rows)

explain select * from enheter where lower(enhetsnamn1) = 'donalds foto ab';
QUERY PLAN ---------------------------------------------------------------------------------------------
Index Scan using idx_enheter_enhetsnamn1 on enheter (cost=0.00..1342.82 rows=337 width=91) Index Cond: (lower(enhetsnamn1) = 'donalds foto ab'::text)
(2 rows)

- Mikael

_____________________________________________________________
Här börjar internet!
Skaffa gratis e-mail och gratis internet på http://www.spray.se

Träffa folk från hela Sverige på ett och samma ställe - http://chat.spray.se/