Thread: Seq scan
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 ) But when I use the EXPLAIN command it always tells me that Postgres is using Seq Scan, even if I disable Seq Scan in the postgresql.conf The PostgreSQL is running on a Pentium III 900 Mhz, 512 MB RAM, HD SCSI 18 Gb with a RedHat Linux 7.3 installed. Anyone can help me? Thanks in advance, Diogo Biazus diogo@ikono.com.br Ikono Sistemas & Automação www.ikono.com.br
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).
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.
Hello all, Is there a way to have the OID included with %ROWTYPE? If not, can the record pointed to by a ROWTYPE record in a loop be updated in place? If not, can the record pointed by a cursor be updated in place? If not can we add one of those to the list of "nice to have" for the next version? While I am at it, why not allow assignment of row variable from another row variable? Thanks JLL
Jean-Luc Lachance <jllachan@nsd.ca> writes:
> Is there a way to have the OID included with %ROWTYPE?
No, because there's no guarantee that a rowtype value actually came
directly from a database row. For instance, it could be formed as
the return value of a function.
regards, tom lane
Well, I found a solution for now. I use RECORD in stead of ROWTYPE. RECORD does hold OID if it exists. JLL P.S. I am still interested about updating a record in place. Jean-Luc Lachance wrote: > > Hello all, > > Is there a way to have the OID included with %ROWTYPE? > If not, can the record pointed to by a ROWTYPE record in a loop be > updated in place? > If not, can the record pointed by a cursor be updated in place? > > If not can we add one of those to the list of "nice to have" for the > next version? > > While I am at it, why not allow assignment of row variable from another > row variable? > > Thanks > > JLL > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
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 claimed victory to quickly. PLPGSQL did compile, run and did not complain about OID but it was always set to 0. JLL Jean-Luc Lachance wrote: > > Well, I found a solution for now. > > I use RECORD in stead of ROWTYPE. > RECORD does hold OID if it exists. > > JLL > > P.S. I am still interested about updating a record in place. > > Jean-Luc Lachance wrote: > > > > Hello all, > > > > Is there a way to have the OID included with %ROWTYPE? > > If not, can the record pointed to by a ROWTYPE record in a loop be > > updated in place? > > If not, can the record pointed by a cursor be updated in place? > > > > If not can we add one of those to the list of "nice to have" for the > > next version? > > > > While I am at it, why not allow assignment of row variable from another > > row variable? > > > > Thanks > > > > JLL > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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
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.