Re: Seq scan

EXPLAIN ANALYZE did the trick! I was afraid to do vacuum analyze
because vacuum takes several hours to complete.

- The type of 'id' is integer, which I believe is the same as int4
- Here is the explain output ( before EXPLAIN ANALYZE )

1) EXPLAIN SELECT my_field,id FROM my_table WHERE id='12168996';
NOTICE: QUERY PLAN:
Seq Scan on my_table (cost=0.00..317684.54 rows=150384
width=16)

2) EXPLAIN UPDATE my_table SET my_field=-1 WHERE id=12168996;
NOTICE: QUERY PLAN:
Seq Scan on my_table (cost=0.00..317684.54 rows=150384
width=22)

3) EXPLAIN SELECT id FROM my_table WHERE id=12168996 ORDER By id;
NOTICE: QUERY PLAN:
Index Scan using my_table_id_idx on my_table
(cost=0.00..400545.08 rows=150384 width=4)

- Here is a portion of EXPLAIN ANALYZE output, which I just ran for the
first time ;)

NOTICE: --Relation my_table --
NOTICE: Pages 129705: Changed 0, reaped 91521, Empty 0, New 0; Tup
15038363: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 984640, MinLen 52, MaxLen
92; Re-using: Free/Avail. Space 2955404/1048348; EndEmpty/Avail. Pages
0/19019. CPU 5.67s/1.04u sec.

-- AFTER EXPLAIN ANALYZE

explain update my_table set status=-1 where id=12168996;
NOTICE: QUERY PLAN:
Index Scan using my_table_id_idx on my_table (cost=0.00..5.00
rows=1 width=22)

Thanks everybody for your quick and helpful responses!

-Stas

-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)com]
Sent: Friday, August 16, 2002 11:21 PM
To: Tom Lane
Cc: stas-pgsql(at)xstas(dot)net; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Seq scan

On Fri, 16 Aug 2002, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On Fri, 16 Aug 2002 stas-pgsql(at)xstas(dot)net wrote:
> >> "SELECT my_field FROM my_table WHERE id=12168996" takes
> >> about half a minute ( 'id' is an sequential indexed field in
> >> my_table )
> >>
> >> EXPLAIN says it is doing a Seq Scan
>
> > Have you vacuum analyzed the table?
>
> I'm betting it's ye olde wrong-datatype issue. What's the data type
> of id? If it's not int4 then a cast is indicated ...

Well, he said that the "solution below did not help" in response to a
message about casting the data type, so I was wondering if it was simply
a default stats case.