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 12:05:02
Message-ID: 44D9CF6E.9030900@avior.ca (view raw or flat)
Thread:
Lists: pgsql-performance
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)


In response to

Responses

pgsql-performance by date

Next:From: Ruben RubioDate: 2006-08-09 12:20:35
Subject: Re: Optimizing queries
Previous:From: Mohan RDate: 2006-08-09 11:58:54
Subject: unsubscribe

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