One down. Total runtime of the simplest query went from 34661.572 ms to
.634 ms (45,000 times faster).
stage=> explain analyze select * from eg_order_summary_view where
invoice_id=1432655;
QUERY
PLAN
"Gregory Stark" <[EMAIL PROTECTED]> writes:
> Nis Jørgensen <[EMAIL PROTECTED]> writes:
>
>> 1. Look up all order_ids for which (order_id,my_invoice_id) appear in
>> eg_orders
>>
>> 2. Find all rows (in both branches of the UNION) with these id_s
Oh, did you mean look up the order_ids for which t
Gregory Stark skrev:
>> 1. Look up all order_ids for which (order_id,my_invoice_id) appear in
>> eg_orders
>>
>> 2. Find all rows (in both branches of the UNION) with these id_s
>>
>> 3. Group the rows, and calculate max(invoice_id)
>>
>> 4. Filter the result rows on max(invoice_id) = my_invoice_i
Nis Jørgensen <[EMAIL PROTECTED]> writes:
>>> Well, the query can be satisfied by looking only at the rows with an
>>> order_id matching the invoice_id given. The condition that this is the
>>> largest invoice in the group then needs to be checked afterwards.
>>>
>>> I certainly did not expect the
Gregory Stark skrev:
> Nis Jørgensen <[EMAIL PROTECTED]> writes:
>
>> Well, the query can be satisfied by looking only at the rows with an
>> order_id matching the invoice_id given. The condition that this is the
>> largest invoice in the group then needs to be checked afterwards.
>>
>> I certainl
Nis Jørgensen <[EMAIL PROTECTED]> writes:
> Well, the query can be satisfied by looking only at the rows with an
> order_id matching the invoice_id given. The condition that this is the
> largest invoice in the group then needs to be checked afterwards.
>
> I certainly did not expect the query pla
Tom Lane skrev:
>> PG apparently is not smart enough to recognize that the
>> result of a max must be one of the values of the column (meaning that it
>> can use an index)
>
> That's because it can't. As written, the query demands sums over groups
> that *include* a specific invoice_id --- but e
Great analysis Gregory & Tom... UNION ALL will make a difference.
---
Here invoices consist of orders, orders consist of order lines. Thus,
each order_id corresponds to just one invoice_id.
One possibility is to add an invoice_i
=?UTF-8?B?TmlzIErDuHJnZW5zZW4=?= <[EMAIL PROTECTED]> writes:
> Bryce Nesbitt skrev:
>> I've got a legacy app with a hefty performance problem.
> It is not clear whether there is a FK relation between eg_order and
> eg_order_line and what the PK of eg_order is.
Or in English: can there really be m
Bryce Nesbitt skrev:
> I've got a legacy app with a hefty performance problem. The basic
> problem is stupid design. It takes 10-15 seconds of CPU time to look up
> an invoice.
> Basically it's trying to mash up extra columns on an otherwise simple
> query, and those extra columns are subtotals.
I've got a legacy app with a hefty performance problem. The basic
problem is stupid design. It takes 10-15 seconds of CPU time to look up
an invoice.
Basically it's trying to mash up extra columns on an otherwise simple
query, and those extra columns are subtotals. Simplified (this looks
best in
11 matches
Mail list logo
