Re: PostgreSQL and VIEWS

On Sat, Mar 23, 2013 at 9:25 PM, Misa Simic <misa(dot)simic(at)gmail(dot)com> wrote:
> HI,
>
> When I have met PostgreSQL for a first time - I have been really amazed -
> with many things...
>
> But how we started to use it - and data jumps in - we meet performance
> problems...
>
> Now, it is a bit tricky... any concrete performance problem - can be solved
> on some way...
>
> However, I am more concerned, that "our way" how we do the things - is not
> best way for Postgres...
>
> The thing is - our tables are highly normalised - so lot of joins... (NULLS
> do not exist in the tables)
>
> to make things simplier we use VIEWS to "denormalise" data - though it is
> not that much we care about denormalisation - it is more to make things
> simplier and less error prone...
>
> So every "thing" (Customer, product, transaction, employee, whatever) is
> built up from more tables... I am not sure we have even one "thing" built up
> just from 1 table...
>
> Lot of "thing properties" are actually calculations: i.e. Invoice Amount, or
> for Employee:
> We have in the one table: first_name and last_name fields, but Full name is
> concatented as:
>
> last_name || ',' || first_name
>
> So, whenever we need employee full name - is it on Employee Info window - or
> in an Invoice as Salesperson, or in Payslip report...instead of to
> everywhere have above formula - we have function...
> but again, instead of to developer think about each possible function for
> concrete thing - we have made for each entity - the view... what have all
> relevant joins - and all relevant calculated properties... about the
> thing...
>
> I have had a thought - somewhere read (but now I am more convenient I have
> read it wrong) that planner is that smart that if we we have:
>
> CREATE VIEW person_view AS
> SELECT person_id, first_name, last_name, fn_full_name(id) as full_name,
> date_of_birth, age(date_of_birth) from person LEFT JOIN
> person_date_of_birth USING (person_id)
>
> SELECT first_name FROM person_view WHERE id = 1
>
> Planner is smart and:
> -will not care about joins - you ask for the field(s) - what do not belong
> to other tables - both belong to 1 - and it is pk!
> -will not care about functions - you havent asked for any field what is
> function in your query...
>
> However - how we met more and more problems with our approach... and
> spending more time on
> EXPLAIN ANALYZE - instead of on business problems... It seems things are not
> that good...
>
> for simple questions as above - results are acceptable - even looking into
> EXPLAIN ANALYZE i would not say it is the best possible plan... (i.e.
> planner spending time on Seq Scan on person_date_of_birth_table - and filter
> it - even no need to think about that table at all - LEFT JOIN (show me
> columns - if there is a matching row for pk column) - so could be check via
> index -however - there is no any column from that table in the query - I
> would simple discard that table from plan....
>
> So query
>
> SELECT id FROM view WHERE id = 5 (view is SELECT * FROM table1 LEFT JOIN
> table2)
>
> I would treat the same as:
> SELECT id FROM table1 = 5
>
> ok in INNER JOIN it requires additional confimration - but even there is FK
> to PK join - that confirmation is not needed iether - but in our cases it is
> always FK to PK...
>
> However - if we need to involve more "entities"/views - from some unknown
> reason to me - postgres always picks bad plan...
>
> i.e. list of employees what work in New York
>
> we have employees_contract table:
> contract_id, person_id, department_id,
>
> a lot of others tables, but to make it shorter:
>
>
> Department_view
>
> Buidlings_view
>
>
> and now query:
> SELECT full_name FROM person_view INNER JOIN emplyee_contract USING
> (person_id) INNER JOIN department_view USING (department_id) INNER JOIN
> buildings_view USING (building_id) WHERE city_id = 'NY'
>
>
> from some unknown reason - gives bad plan - then if we "refactor" query and
> send different question - we get good result... I am pretty sure planner
> should be capable to "rephrase" my question instead of me...
>
> I would like to hear your expirience with VIEWS in postgres... And some kind
> of best practice/advice for described situation... So far it looks to me
> there is no way - to make things ready for any specific question - every
> "request" will need specific SQL syntax to drive planner in acceptable
> direction...

You asked some broad questions so you are going to get broad answers.

*) query planner is very complicated and changes are very incremental.
only a very, very small number of people (Tom especially) are capable
of making major changes to it. some known planner issues that might
get fixed in the short term are better handling of quals through UNION
ALL and/or pushing quals through partitioned windows functions. these
are documented shortcomings -- other improvements have to be looked
through the lens of 'what else did you break', including,
unfortunately, plan time.

*) filtering in predicate expressions (select * from foo where a || b
= 'x') is going to always suck. consider optimizing via indexes
and/or materialization

*) excessive use of views is for data abstraction (a technique I
advocate) is on one side of a tradeoff between materlization and run
time performance. if you are exceeding your ability to quickly write
clean queries, perhaps it's time to start thinking about some
materialization steps. especially you should be asking yourself if
users are needing 100% up to date data (if not, materialization
requirements relax significantly)

*) better, more experienced programmers write faster, cleaner code.
this is true in all backends. @ two years in, you have some learning
to do that -hackers will not be able to solve for you.

merlin