Andy wrote: > I have been pulling my hair out over the last few days trying to get any useful performance out of the > following > painfully slow query. > The query is JPA created, I've just cleaned the aliases to make it more readable. > Using 'distinct' or 'group by' deliver about the same results, but 'distinct' is marginally better. > Hardware is pretty low end (a test box), but is mostly dedicated to PostgreSQL. > The box spec and configuration is included at the end of this post - Some of the values have been > changed just to see if > things get better. > Inserts have also become extremely slow. I was expecting a drop off when the database grew out of > memory, but not this much. > > Am I really missing the target somewhere? > Any help and or suggestions will be very much appreciated. > > Best regards, > > Andy. > > http://explain.depesz.com/s/cfb The estimate on the join between recipe_version and test_result is not good. Maybe things will improve if you increase the statistics on test_result.id_recipe_version. If that does not help, maybe the nested loop join that takes all your time can be sped up with the following index: CREATE INDEX any_name ON test_item (id_test_result, type); But I would not expect much improvement there. BTW, you seem to have an awful lot of indexes defined, some of which seem redundant. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
