Re: Seq scan
От | |
---|---|
Тема | Re: Seq scan |
Дата | |
Msg-id | 015d01c24558$f43e2ff0$0cca4c3e@VAIO обсуждение исходный текст |
Ответ на | Re: Seq scan (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Список | pgsql-general |
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@megazone23.bigpanda.com] Sent: Friday, August 16, 2002 11:21 PM To: Tom Lane Cc: stas-pgsql@xstas.net; pgsql-general@postgresql.org Subject: Re: [GENERAL] Seq scan On Fri, 16 Aug 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > On Fri, 16 Aug 2002 stas-pgsql@xstas.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.
В списке pgsql-general по дате отправления: