Re: PostgreSQL and VIEWS

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.simic@gmail.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...