From: Svenne Krap <svenne@krap.dk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PgSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: multi-layered view join performance oddities
Date: Sun, 30 Oct 2005 19:49:10 +0100
Message-ID: <436515A6.8040909@krap.dk> (raw)
In-Reply-To: <13993.1130696821@sss.pgh.pa.us>
References: <4364FFD4.2020801@krap.dk>
<13993.1130696821@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: svenne@krap.dk, tgl@sss.pgh.pa.us
Subject: Re: multi-layered view join performance oddities
In-Reply-To: <436515A6.8040909@krap.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox