Skip site navigation (1) Skip section navigation (2)

Re: multi-layered view join performance oddities

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Svenne Krap <svenne(at)krap(dot)dk>
Cc: PgSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: multi-layered view join performance oddities
Date: 2005-10-30 18:27:01
Message-ID: 13993.1130696821@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Svenne Krap <svenne(at)krap(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Svenne KrapDate: 2005-10-30 18:33:03
Subject: Re: multi-layered view join performance oddities
Previous:From: Steinar H. GundersonDate: 2005-10-30 17:44:50
Subject: Re: multi-layered view join performance oddities

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group