Message-ID: <44D9D313.5060804@rentalia.com>
Date: Wed, 09 Aug 2006 14:20:35 +0200
From: Ruben Rubio <ruben@rentalia.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060728)
MIME-Version: 1.0
To: Patrice Beliveau <pbeliveau@avior.ca>
CC: 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>
In-Reply-To: <44D9CF6E.9030900@avior.ca>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

-----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-----