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