Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-24 Thread Tom Lane
Viktor Rosenfeld <[EMAIL PROTECTED]> writes: > Postgres is indeed selecting a bad plan. Turns out that the index I > created to speed up the UPDATE isn't used inside a transaction block. I was having a hard time believing that, but just noticed that there is a case in which it could be expected

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-18 Thread Tom Lane
Viktor Rosenfeld <[EMAIL PROTECTED]> writes: > Postgres is indeed selecting a bad plan. Turns out that the index I > created to speed up the UPDATE isn't used inside a transaction block. That doesn't make any sense to me, and in fact I cannot replicate any such behavior here. What PG version a

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-16 Thread Viktor Rosenfeld
Yes, I either run begin; ... lots of other stuff in a script explain analyze update ... (called from the same script) rollback; or ... lots of other stuff in a script (same as above) explain analyze update ... Cheers, Viktor Am 16.07.2008 um 16:58 schrieb Pavel Stehule: this is strange. wh

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-16 Thread Viktor Rosenfeld
Hi, I have no idea why the trigger constraints are called in the first place since the respective columns are not touched in the query. Also with the old correlated subquery these trigger constraints were not called either. Cheers, Viktor Am 16.07.2008 um 17:01 schrieb Pavel Stehule:

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-16 Thread Pavel Stehule
hello second query: why without transactions are not called triggers constraint _FK_struct_2_collection and constraint _FK_struct_2_text? Regards Pavel Stehule 2008/7/16 Viktor Rosenfeld <[EMAIL PROTECTED]>: > Hi Pavel, > > thanks for the advice on how to uncorrelate the query. I must admit I

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-16 Thread Pavel Stehule
this is strange. what means "run under transaction"? you did exactly statements in psql console: begin; explain analyze select ... commit? regards Pavel Stehule 2008/7/16 Viktor Rosenfeld <[EMAIL PROTECTED]>: > Hi Pavel, > > thanks for the advice on how to uncorrelate the query. I must admit I

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-16 Thread Viktor Rosenfeld
Hi Pavel, thanks for the advice on how to uncorrelate the query. I must admit I didn't know about the UPDATE ... SET ... FROM ... syntax. Now the UPDATE runs in an acceptable time inside a transaction, however the query plan still differs when I run it outside. Outside a transaction:

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-16 Thread Pavel Stehule
Hello my advice is little bit offtopic, I am sorry. Why you use correlated subquery? Your update statement should be update _struct set left_token = tmp.left_token from tmp where _struct.id = tmp.id; send output of explain analyze statement, please. etc explain analyze UPDATE _struct SET left_to

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-16 Thread Viktor Rosenfeld
Hi Tom, Postgres is indeed selecting a bad plan. Turns out that the index I created to speed up the UPDATE isn't used inside a transaction block. Here's the plan for "UPDATE _struct SET left_token = (SELECT DISTINCT left_token FROM tmp WHERE _struct.id = tmp.id)" outside of a transaction

Re: [GENERAL] UPDATE runs slow in a transaction

2008-07-14 Thread Tom Lane
Viktor Rosenfeld <[EMAIL PROTECTED]> writes: > the script below runs very fast when executed alone. But when I call > it from within a transaction block it's so slow that I have to abort > it after a while. Specifically the second-to-last UPDATE seems to > take forever within a transaction