Message-ID: <44D9EFC5.4070708@avior.ca>
Date: Wed, 09 Aug 2006 10:23:01 -0400
From: Patrice Beliveau <pbeliveau@avior.ca>
User-Agent: Thunderbird 1.5.0.4 (X11/20060614)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Optimizing queries
References: <44D8CE92.1080407@avior.ca>
	<1155062399.20252.50.camel@state.g2switchworks.com>
	<44D8F0B8.2030804@avior.ca> <13030.1155069749@sss.pgh.pa.us>
	<44D9CF6E.9030900@avior.ca> <44D9D313.5060804@rentalia.com>
In-Reply-To: <44D9D313.5060804@rentalia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I've create a view, same query plan (some number vary a bit, but nothing 
significant) and same result, closed sales_order are processed

Ruben Rubio wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> If subquerys are not working I think you should try to create a view
> with the subquery.
>
> Maybe it will work.
>
> Patrice Beliveau wrote:
>   
>> Tom Lane wrote:
>>     
>>> Patrice Beliveau <pbeliveau@avior.ca> writes:
>>>  
>>>       
>>>>>> SELECT * FROM TABLE
>>>>>> WHERE TABLE.COLUMN1=something
>>>>>> AND TABLE.COLUMN2=somethingelse
>>>>>> AND function(TABLE.COLUMN3,TABLE.COLUMN4) > 0;
>>>>>>         
>>>>>>             
>>>  
>>>       
>>>> I find out that the function process every row even if the row should
>>>> be rejected as per the first or the second condition.
>>>> ... I'm using version 8.1.3
>>>>     
>>>>         
>>> PG 8.1 will not reorder WHERE clauses for a single table unless it has
>>> some specific reason to do so (and AFAICT no version back to 7.0 or so
>>> has done so either...)  So there's something you are not telling us that
>>> is relevant.  Let's see the exact table schema (psql \d output is good),
>>> the exact query, and EXPLAIN output for that query.
>>>
>>>             regards, tom lane
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>>        choose an index scan if your joining column's datatypes do not
>>>        match
>>>
>>>
>>>   
>>>       
>> Hi,
>>
>> here is my query, and the query plan that result
>>
>> explain select * from (
>>   select * from sales_order_delivery
>>       where sales_order_id in (
>>               select sales_order_id from sales_order
>>               where closed=false
>>       )
>> ) as a where outstandingorder(sales_order_id, sales_order_item,
>> date_due) > 0;
>>
>>
>>                                                      QUERY PLAN
>> ----------------------------------------------------------------------------------------------------------------------
>>
>> Hash IN Join  (cost=498.89..8348.38 rows=34612 width=262)
>>   Hash Cond: (("outer".sales_order_id)::text =
>> ("inner".sales_order_id)::text)
>>   ->  Seq Scan on sales_order_delivery  (cost=0.00..6465.03 rows=69223
>> width=262)
>>         Filter: (outstandingorder((sales_order_id)::text,
>> (sales_order_item)::text, date_due) > 0::double precision)
>>   ->  Hash  (cost=484.90..484.90 rows=5595 width=32)
>>         ->  Seq Scan on sales_order  (cost=0.00..484.90 rows=5595 width=32)
>>               Filter: (NOT closed)
>> (7 rows)
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>>
>>     
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.2.2 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFE2dMTIo1XmbAXRboRAhbIAJwJGZ+ITP0gl38A3qROrzIeNbTtUwCcDOIW
> eZ9NJqjL+58gyMfO95jwZSw=
> =4Zxj
> -----END PGP SIGNATURE-----
>
>
>