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

Re: Optimizing queries

From: Patrice Beliveau <pbeliveau(at)avior(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimizing queries
Date: 2006-08-09 14:23:01
Message-ID: 44D9EFC5.4070708@avior.ca (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)avior(dot)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-----
>
>
>   


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2006-08-09 14:39:06
Subject: Re: Optimizing queries
Previous:From: Michal Taborsky - Internet MallDate: 2006-08-09 14:06:35
Subject: 3-table query optimization

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