Re: Optimizing queries

Поиск
Список
Период
Сортировка
От Patrice Beliveau
Тема Re: Optimizing queries
Дата
Msg-id 44D9EFC5.4070708@avior.ca
обсуждение исходный текст
Ответ на Re: Optimizing queries  (Ruben Rubio <ruben@rentalia.com>)
Список 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@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)
>>
>>
>> ---------------------------(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-----
>
>
>


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Merlin Moncure"
Дата:
Сообщение: Re: Hardware upgraded but performance still ain't good enough
Следующее
От: Michal Taborsky - Internet Mall
Дата:
Сообщение: 3-table query optimization