[SQL] EXPLAIN ANALYZE inside functions
Hi there, I'd like to rise the performance of a custom function, but I read somewhere I cannot use EXPLAIN ANALYZE to get the execution plan of the code from the function. Do you know a workaround to do this ? I tried to apply EXPLAIN ANALYZE statements inside my procedure hopping I will get some notice messages, but I got nothing :(. TIA, Sabin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] EXPLAIN ANALYZE inside functions
> > Do you know a workaround to do this ? > I just succeeded adding the following code: FOR rec IN EXECUTE 'EXPLAIN ANALYZE ' || sSQL LOOP RAISE NOTICE ' - %', rec; END LOOP; Regards, Sabin ---(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
[SQL] Aggregate question (Sum)
Hi All, I want to know if there are an easy manner to do an SQL like this bellow where TotalOrdersValue sum the order.total just one time per order (as count(DISTINCT order.id) do) SELECT order.dtorder , Count( DISTINCT order.fk_customer ) AS QtyCustomer , Count( DISTINCT order.id ) AS QtyOrder , Sum( order_item.qty ) AS TotalQtyItem , Sum( order.total ) AS TotalOrders FROM order JOIN order_item ON order_item.fk_order = order.id GROUP BY 1 ORDER BY 1 Ex. ORDER Id | dtorder| fk_customer | total - 1 | 2007-01-01 | 1 | 100.00 2 | 2007-01-01 | 1 | 30.00 order_item fk_order | qty | fk_product 1 |5 | A 1 |2 | B 2 |3 | C The query acctualy returns (as expected): dtorder | QtyCustomer | QtyOrder | TotalQtyItem | TotalOrders -- 2007-01-01 | 1 |2 | 10 | 230.00 But I want dtorder | QtyCustomer | QtyOrder | TotalQtyItem | TotalOrders -- 2007-01-01 | 1 |2 | 10 | 130.00 I just want to avoid to do, if possible, things like: SELECT totals.dtorder , totals.QtyCustomer , totals.QtyOrder , totals.TotalQtyItem , Sum( order.total ) AS TotalOrders FROM ( SELECT order.dtorder , Count( DISTINCT order.fk_customer ) AS QtyCustomer , Count( DISTINCT order.id ) AS QtyOrder , Sum( order_item.qty ) AS TotalQtyItem FROM order JOIN order_item ON order_item.fk_order = order.id GROUP BY 1 ) totals JOIN order ON order.dtorder = totals.dtorder GROUP BY 1,2,3,4 ORDER BY totals.dtorder I say this because it's seem a waste of effort just to sum a value that can be calculated on the same loop where postgresql will go on table order... If someone can give me some hint I will apreciate. Tanks in advance. -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Aggregate question (Sum)
On Nov 19, 2007 3:46 PM, Luiz K. Matsumura <[EMAIL PROTECTED]> wrote: > If someone can give me some hint I will apreciate. This is more of a normalization problem. See: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx and lookup Third Normal Form. You have a calculated total for each order, so I assume there's a price for every product. You should join order_item to whichever table has the individual cost for each product and multiply it by order_item.qty. Good luck. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Aggregate question (Sum)
Hi Rodrigo, thanks for reply Rodrigo De León wrote: On Nov 19, 2007 3:46 PM, Luiz K. Matsumura <[EMAIL PROTECTED]> wrote: If someone can give me some hint I will apreciate. This is more of a normalization problem. See: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx and lookup Third Normal Form. You have a calculated total for each order, so I assume there's a price for every product. You should join order_item to whichever table has the individual cost for each product and multiply it by order_item.qty. Ok, the example is a simplified scenario. In some cases we have discount on order that can't be 'distributed' on the itens. For example: Item Qty ValueUnity item_total (calculated) 15 1.01 5.05 26 1.01 6.06 The total for itens is 11.11 but we make a discount of 0.01 , then the value of order is now 11.10 This 0.01 of discount can't be aplied on any "ValueUnity" because the precision is 2 decimals. We can have too other calculations over this values (like taxes) so in my case isn't so simple to determine the final value of the order just summing the value of the itens. Anyway, thanks again... -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
