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

Re: Slow update with simple query

From: "Jens Schipkowski" <jens(dot)schipkowski(at)apus(dot)co(dot)at>
To: "Arnaud Lesauvage" <thewild(at)freesurf(dot)fr>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow update with simple query
Date: 2006-12-13 13:20:40
Message-ID: op.tkh10qgw81rjf6@xjens.apus.local (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 13 Dec 2006 13:23:41 +0100, Arnaud Lesauvage <thewild(at)freesurf(dot)fr>  
wrote:

> Jens Schipkowski a écrit :
>> the problem is a combination of bad formed SQL and maybe missing  
>> indexes.
>> try this:
>> UPDATE t1
>> SET booleanfield = foo.bar
>>  FROM (SELECT uid,(field IN ('some','other') AND field2 = 'Y') AS bar  
>> FROM  t2) AS foo
>> WHERE t1.uid=foo.uid;
>
>
> Hi Jens,
> Why is this query better than the other one ? Because it runs the  
> "(field IN ('some','other') AND field2 = 'Y')" once and then executes  
> the join with the resulting set ?
True. The Subselect in FROM clause will be executed once and will be  
joined using the condition at where clause. So your condition at t2 is not  
executed for each row in t1(2mio records) but for each row in t2(1k  
records). And the boolean value is already set during update.

regards,
Jens

>
>> and index t1.uid, t2.uid, t2.field, t2.field2
>
> t1.field can only take 3 or 4 values (don't remember exactly), and  
> field2 only 2 ('Y' or 'N'). So this fields have a very low cardinality.
> Won't the planner chose to do a table scan in such a case ?
>
> Thanks for your advices !
>
> --
> Arnaud



-- 
**
APUS Software GmbH

In response to

Responses

pgsql-performance by date

Next:From: Arnaud LesauvageDate: 2006-12-13 13:38:37
Subject: Re: Slow update with simple query
Previous:From: Arnaud LesauvageDate: 2006-12-13 12:23:41
Subject: Re: Slow update with simple query

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