From: Patrice Beliveau <pbeliveau@avior.ca>
To: pgsql-performance@postgresql.org
Subject: Re: Optimizing queries
Date: Wed, 09 Aug 2006 08:05:02 -0400
Message-ID: <44D9CF6E.9030900@avior.ca> (raw)
In-Reply-To: <13030.1155069749@sss.pgh.pa.us>
References: <44D8CE92.1080407@avior.ca>
<1155062399.20252.50.camel@state.g2switchworks.com>
<44D8F0B8.2030804@avior.ca>
<13030.1155069749@sss.pgh.pa.us>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pbeliveau@avior.ca
Subject: Re: Optimizing queries
In-Reply-To: <44D9CF6E.9030900@avior.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox