Skip site navigation (1) Skip section navigation (2)

Re: Slow UPADTE, compared to INSERT

From: Ivar Zarans <iff(at)alcaron(dot)ee>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow UPADTE, compared to INSERT
Date: 2003-12-05 02:07:28
Message-ID: 20031205020728.GA21440@alcaron.ee (view raw or flat)
Thread:
Lists: pgsql-performance
I have played around with explain and explain analyze and noticed one
interesting oddity:

===
explain UPDATE table1 SET status = 'SKIP' WHERE recid = 196641;

 Seq Scan on table1 (cost=0.00..16709.97 rows=1 width=199)
 Filter: (recid = 196641)

=== 

explain UPDATE table1 SET status = 'SKIP' WHERE recid = '196641';
 
 Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=199)
 Index Cond: (recid = 196641::bigint)

===

explain UPDATE table1 SET status = 'SKIP' WHERE recid = 196641::bigint;
 
 Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=199)
 Index Cond: (recid = 196641::bigint)
    
===

Why first example, where recid is given as numeric constant, is using
sequential scan, but second example, where recid is given as string
constant works with index scan, as expected? Third example shows, that
numeric constant must be typecasted in order to function properly.

Is this normal behaviour of fields with bigint type?

-- 
Ivar Zarans


In response to

Responses

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2003-12-05 02:15:52
Subject: Re: Slow UPADTE, compared to INSERT
Previous:From: Ivar ZaransDate: 2003-12-05 01:45:14
Subject: Re: Slow UPADTE, compared to INSERT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group