Hi Serge,
here are the some examples I posted in PERFORM list:
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:
"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.fonville@xxxxxxxxx>
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?HTHKind regards/met vriendelijke groet,Serge Fonvillehttp://www.sergefonville.nl
Convince Microsoft!They need to add TRUNCATE PARTITION in SQL Server2013/3/24 Misa Simic <misa.simic@xxxxxxxxx>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_nameSo, 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 ASSELECT 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 = 1Planner 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 onEXPLAIN 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 querySELECT id FROM view WHERE id = 5 (view is SELECT * FROM table1 LEFT JOIN table2)I would treat the same as:SELECT id FROM table1 = 5ok 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 Yorkwe have employees_contract table:contract_id, person_id, department_id,a lot of others tables, but to make it shorter:Department_viewBuidlings_viewand 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...
