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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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