[PERFORM] Slow UPDATE, INSERT OK

2003-12-09 Thread Ivar Zarans
nly primary key, which is used on update. Am i doing something wrong or is this normal? I am using PostgreSQL 7.3.4, Debian/GNU Linux 3.0 (Woody), kernel 2.4.21, Python 2.3.2, PyPgSQL 2.4 -- Ivar Zarans ---(end of broadcast)--- TIP 9: the plan

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-05 Thread Ivar Zarans
On Fri, Dec 05, 2003 at 07:21:38PM +0530, Shridhar Daithankar wrote: > planner consider seemingly same type as different. e.g. treating int8 as > different than int4. Obvious thinking is they should be same. But given > postgresql's flexibility with create type, it is difficult to promote. OK,

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-05 Thread Ivar Zarans
On Fri, Dec 05, 2003 at 01:23:43PM +, Richard Huxton wrote: > Better IMHO would be: "UPDATE table1 SET status = %s WHERE recid = %s::int8" Thanks for the hint! > unexpectedly in this scenario. The reason is that the literal number is > treated as int4, whereas quoted it is marked as type un

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-05 Thread Ivar Zarans
On Fri, Dec 05, 2003 at 06:19:46PM +0530, Shridhar Daithankar wrote: > >is correct SQL, but not correct, considering PostgreSQL bugs. > > Personally I don't consider a bug but anyways.. You are the one facing > problem so I understand.. Well, if this is not bug, then what is consideration behin

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-05 Thread Ivar Zarans
On Fri, Dec 05, 2003 at 10:08:20AM +, Richard Huxton wrote: > > numeric constant must be typecasted in order to function properly. > > > > Is this normal behaviour of fields with bigint type? > > As Christopher says, normal (albeit irritating). Not sure it applies here - > all the examples y

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-04 Thread Ivar Zarans
constant must be typecasted in order to function properly. Is this normal behaviour of fields with bigint type? -- Ivar Zarans ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-04 Thread Ivar Zarans
:rows 1 postgres[21247]: [2710-6]:width 199 postgres[21247]: [2710-7]:qptargetlist ( ... Skipped target list ... postgres[21247]: [2711] DEBUG: CommitTransactionCommand postgres[21247]: [2712] LOG: duration: 0.292529 sec === Any suggestions for further investigation? -- Ivar Zara

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-04 Thread Ivar Zarans
On Thu, Dec 04, 2003 at 08:23:36PM +, Richard Huxton wrote: > Ah - it's probably not the update but the IN. You can rewrite it using PG's > non-standard FROM: > > UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_id; This was one *very useful* hint! Using this method i got

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-04 Thread Ivar Zarans
7;; Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=198) Index Cond: (recid = 199901::bigint) (2 rows) -- Ivar Zarans ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropri

Re: [PERFORM] Slow UPADTE, compared to INSERT

2003-12-04 Thread Ivar Zarans
ATE table1 SET Status = 'done' WHERE recid IN (SELECT recid FROM temptable)". It is still slower than INSERT, but more or less acceptable. Compared to my first tests overall processing time dropped from 1 hour and 20 minutes to 16 minutes. So, my question remains - why is simple up

[PERFORM] Slow UPADTE, compared to INSERT

2003-12-04 Thread Ivar Zarans
nly primary key, which is used on update. Am i doing something wrong or is this normal? I am using PostgreSQL 7.3.4, Debian/GNU Linux 3.0 (Woody), kernel 2.4.21, Python 2.3.2, PyPgSQL 2.4 -- Ivar Zarans ---(end of broadcast)--- TIP 1: subscribe