ugly query slower in 7.3, even slower after vacuum full analyze
От | SZŰCS Gábor |
---|---|
Тема | ugly query slower in 7.3, even slower after vacuum full analyze |
Дата | |
Msg-id | 008101c3206e$0dc8a310$0403a8c0@fejleszt4 обсуждение исходный текст |
Ответы |
Re: ugly query slower in 7.3, even slower after vacuum full analyze
Re: ugly query slower in 7.3, even slower after vacuum full analyze |
Список | pgsql-performance |
Dear Gurus, This is a rather nasty query, built up from several parameters, and it proved to be 7--15 times slower in 7.3 than in 7.2. This particular query takes more than 3.5 minutes (4 after vacuum full analyze! (henceforth VFA)) that is unacceptable in an interactive client application. If you have courage and will to please have a look at the query and/or the explains, you might point out something I can't see at this level of complexity. As for trivial questions: * The databases were identical a couple of weeks ago, deviated slightly since then, but I don't think it may be a cause. * The 5% difference in the result set doesn't seem to explain this huge diff in performance either. * The query has been run on the same server (Linux RedHat 6.1 -- historical, isn't it?) with the same load (this one postmaster took >90% CPU all the time, in all three cases) * Since this query involves quite a large part of the database, I'm not willing to post a dump on the list. If a schema-only dump helps, I may be able to send it in private email; I approximate it to be ~500k, zipped. * Also checked a "lighter" version of this query (at least, fewer rows). It took 223msec on 7.2 and 3658 on 7.3 (VFA). (15x slower) However, it got down to 400-500msec (still double of 7.2) when re-queried Files are zipped, since 7.3 exp-ana's are over 40k each. slow.sql: the query. 72.ana: explain analyze in 7.2 73.ana: explain analyze in 7.3, before VFA 73.ana2: explain analyze in 7.3, after VFA I just hope someone helps me; any little help may prove really useful! TIA, G. ------------------------------- cut here -------------------------------
Вложения
В списке pgsql-performance по дате отправления: