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