Re: PostgreSQL and VIEWS

Hi Serge,

here are the some examples I posted in PERFORM list:

http://postgresql.1045698.n5.nabble.com/PostgreSQL-planner-tp5749427.html

No - I do not compare Pg to anything - I am developer - before, I used SQL
Server - but two years ago I moved to Postgres - and I have no idea what
SQL Server does now - neither I have time to do compare...

I just said something what is "logical" to me,,, "How little man imagine
world should work"

Neither I have been thinking about planner before - because of always got
results in acceptable time - just now when we have more data - we meet
problems - and solution is always "rephrase" the question for particular
thing...

But here is example about similar thing what someone else have:

http://postgresql.1045698.n5.nabble.com/Performance-of-query-td5749320.html

"rephrase the question" algorythm - from my developer perspective - is not
hard task to do - and it is something what we will need to do to solve our
problem... But I think it is more generic thing what would be good for
Postgres in general....

Thanks,

Misa

:

2013/3/24 Serge Fonville <serge(dot)fonville(at)gmail(dot)com>

> Hi a couple of things:
>
> 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...
>
>
> You seem to be comparing PostgreSQL performance to some other
> implementation, could you share which one that is?
> Have you defined things like Foreign Key constraints, Indexes, Statistics?
> Also could you share some queries and their plans?
>
> HTH
>
> Kind regards/met vriendelijke groet,
>
> Serge Fonville
>
> http://www.sergefonville.nl
>
> Convince Microsoft!
> They need to add TRUNCATE PARTITION in SQL Server
>
> https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table
>
>
> 2013/3/24 Misa Simic <misa(dot)simic(at)gmail(dot)com>
>
>> 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...
>>
>> Many thanks,
>>
>> Misa...
>>
>
>