From: Tom Lane <tgl@sss.pgh.pa.us>
To: Svenne Krap <svenne@krap.dk>
Cc: PgSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: multi-layered view join performance oddities
Date: Sun, 30 Oct 2005 13:27:01 -0500
Message-ID: <13993.1130696821@sss.pgh.pa.us> (raw)
In-Reply-To: <4364FFD4.2020801@krap.dk>
References: <4364FFD4.2020801@krap.dk>
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.
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.
regards, tom lane
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: tgl@sss.pgh.pa.us, svenne@krap.dk
Subject: Re: multi-layered view join performance oddities
In-Reply-To: <13993.1130696821@sss.pgh.pa.us>
* 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