X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0FB5EDB436 for ; Sun, 30 Oct 2005 14:48:08 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05704-02 for ; Sun, 30 Oct 2005 18:48:06 +0000 (GMT) Received: from cicero1.cybercity.dk (cicero1.cybercity.dk [212.242.40.4]) by svr1.postgresql.org (Postfix) with ESMTP id D6240DB434 for ; Sun, 30 Oct 2005 14:48:05 -0400 (AST) Received: from [192.168.0.79] (0x3e42eeca.adsl.cybercity.dk [62.66.238.202]) by cicero1.cybercity.dk (Postfix) with ESMTP id 1B0CA7E39EB; Sun, 30 Oct 2005 19:48:04 +0100 (CET) Message-ID: <436515A6.8040909@krap.dk> Date: Sun, 30 Oct 2005 19:49:10 +0100 From: Svenne Krap User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: PgSQL Performance Subject: Re: multi-layered view join performance oddities References: <4364FFD4.2020801@krap.dk> <13993.1130696821@sss.pgh.pa.us> In-Reply-To: <13993.1130696821@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------010502090203050105080605" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.095 required=5 tests=[AWL=0.094, HTML_MESSAGE=0.001] X-Spam-Score: 0.095 X-Spam-Level: X-Archive-Number: 200510/465 X-Sequence-Number: 15233 This is a multi-part message in MIME format. --------------010502090203050105080605 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >Svenne Krap writes: > > >>create view ord_institutes_sum as >> SELECT ord_property_type_all.dataset_id, ord_property_type_all.nb_property_type_id, 0 AS institut, sum(ord_property_type_all.amount) AS amount >> FROM ord_property_type_all >> GROUP BY ord_property_type_all.dataset_id, ord_property_type_all.nb_property_type_id; >> >> > > > >>create view ord_result_pct as >> SELECT t1.dataset_id, t1.nb_property_type_id, t1.institut, t1.amount / t2.amount * 100::numeric AS pct >> FROM ord_property_type_all t1, ord_institutes_sum t2 >> WHERE t1.dataset_id = t2.dataset_id AND t1.nb_property_type_id = t2.nb_property_type_id; >> >> > >This is really pretty horrid code: you're requesting double evaluation >of the ord_property_type_all view, and then joining the two calculations >to each other. No, the planner will not detect how silly this is :-(, >nor will it realize that there's guaranteed to be a match for every row >--- I believe the latter is the reason for the serious misestimation >that Steinar noted. The misestimation doesn't hurt particularly when >evaluating ord_result_pct by itself, because there are no higher-level >decisions to make ... but it hurts a lot when you join ord_result_pct to >some other stuff. > > I don't really see, how this query is horrid from a user perspective, this is exactly the way, the percentage has to be calculated from a "philosophical" standpoint (performance considerations left out). This is very bad news for me, as most of the other (much larger) queries have the same issue, that the views will be used multiple times got get slightly different data, that has to be joined (also more than 2 times as in this case) I think, it has to run multiple times as it returns two different types of data. >It seems like there must be a way to get the percentage amounts with >only one evaluation of ord_property_type_all, but I'm not seeing it >right offhand. > > I will think about how to remove the second evaluation of the view in question, if anyone knows how, a hint is very appriciated :) I could of course go the "materialized view" way, but would really prefer not to. Svenne --------------010502090203050105080605 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Tom Lane wrote: 
Svenne Krap <svenne@krap.dk> writes:
  
create view ord_institutes_sum as
 SELECT ord_property_type_all.dataset_id, ord_property_type_all.nb_property_type_id, 0 AS institut, sum(ord_property_type_all.amount) AS amount
   FROM ord_property_type_all
 GROUP BY ord_property_type_all.dataset_id, ord_property_type_all.nb_property_type_id;
    

  
create view ord_result_pct as
 SELECT t1.dataset_id, t1.nb_property_type_id, t1.institut, t1.amount / t2.amount * 100::numeric AS pct
   FROM ord_property_type_all t1, ord_institutes_sum t2
  WHERE t1.dataset_id = t2.dataset_id AND t1.nb_property_type_id = t2.nb_property_type_id;
    

This is really pretty horrid code: you're requesting double evaluation
of the ord_property_type_all view, and then joining the two calculations
to each other.  No, the planner will not detect how silly this is :-(,
nor will it realize that there's guaranteed to be a match for every row
--- I believe the latter is the reason for the serious misestimation
that Steinar noted.  The misestimation doesn't hurt particularly when
evaluating ord_result_pct by itself, because there are no higher-level
decisions to make ... but it hurts a lot when you join ord_result_pct to
some other stuff.
I don't really see, how this query is horrid from a user perspective, this is exactly the way, the percentage has to be calculated from a "philosophical" standpoint (performance considerations left out).
This is very bad news for me, as most of the other (much larger) queries have the same issue, that the views will be used multiple times got get slightly different data, that has to be joined (also more than 2 times as in this case)

I think, it has to run multiple times as it returns two different types of data. 

It seems like there must be a way to get the percentage amounts with
only one evaluation of ord_property_type_all, but I'm not seeing it
right offhand.

I will think about how to remove the second evaluation of the view in question, if anyone knows how, a hint is very appriciated :)

I could of course go the "materialized view" way, but would really prefer not to.

Svenne
--------------010502090203050105080605--