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.fonvi...@gmail.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.si...@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... >> > >
