Re: Seq scan
On Thu, 15 Aug 2002, Diogo Biazus wrote:
Hi everybody.
Im trying to execute a simple UPDATE query on a table with 450000 rows
using an index in the where clause.Example:
UPDATE table SET field = null WHERE field = 12345
( NOTICE: Field has an index )
If field is int8, you'll need to either quote the number or
cast it to int8 (either ::int8 or cast(12345 as int8) should work).
Import Notes
Reply to msg id not found: 3D5C0277.6020506@ikono.com.br
On Thu, 15 Aug 2002, Stephan Szabo wrote:
On Thu, 15 Aug 2002, Diogo Biazus wrote:
Hi everybody.
Im trying to execute a simple UPDATE query on a table with 450000 rows
using an index in the where clause.Example:
UPDATE table SET field = null WHERE field = 12345
( NOTICE: Field has an index )If field is int8, you'll need to either quote the number or
cast it to int8 (either ::int8 or cast(12345 as int8) should work).
(Slow today) The same is true for int2 (which is despite my braindamage
today large enough to hold 12345) except that you cast to int2.
Stephan Szabo wrote:
On Thu, 15 Aug 2002, Stephan Szabo wrote:
On Thu, 15 Aug 2002, Diogo Biazus wrote:
Hi everybody.
Im trying to execute a simple UPDATE query on a table with 450000 rows
using an index in the where clause.Example:
UPDATE table SET field = null WHERE field = 12345
( NOTICE: Field has an index )If field is int8, you'll need to either quote the number or
cast it to int8 (either ::int8 or cast(12345 as int8) should work).(Slow today) The same is true for int2 (which is despite my braindamage
today large enough to hold 12345) except that you cast to int2.---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Yes, the field is int8 and now its working fine (with the quotes),
thanks for the help.
Diogo Biazus
I am having a similar problem except that the solution below did not
help. I have a table with about 15 million records.
"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
However, when I add ORDER BY id:
"SELECT my_field FROM my_table WHERE id=12168996 ORDER BY id"
returns instantly
In this case, EXPLAIN says the index is used.
Now, this workaround works fine for SELECTs, but UPDATEs cause the same
problem and I cannot specify something like ORDER BY.
Why is it doing it, and how can I force it to use the index in case of
UPDATEs? It seems to me the index should always be used in the SELECT
and UPDATE examples above!!
I am using postgres version 7.1
Thanks
-Stas
--------------------------------------------------------
Stephan Szabo wrote:
On Thu, 15 Aug 2002, Stephan Szabo wrote:
On Thu, 15 Aug 2002, Diogo Biazus wrote:
Hi everybody.
Im trying to execute a simple UPDATE query on a table with 450000 rows
using an index in the where clause.
Example:
UPDATE table SET field = null WHERE field = 12345
( NOTICE: Field has an index )
If field is int8, you'll need to either quote the number or
cast it to int8 (either ::int8 or cast(12345 as int8) should work).
(Slow today) The same is true for int2 (which is despite my braindamage
today large enough to hold 12345) except that you cast to int2.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Yes, the field is int8 and now its working fine (with the quotes),
thanks for the help.
Diogo Biazus
Import Notes
Resolved by subject fallback
On Fri, 16 Aug 2002 stas-pgsql@xstas.net wrote:
I am having a similar problem except that the solution below did not
help. I have a table with about 15 million records."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?
Can we see the table's structure and the explain output and number of
row in the table? Specifically, it's interesting to see what the row
estimate isrelative to the number of rows really returned and the number
of rows in the table.
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 ...
regards, tom lane
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.
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.