Question about performance of planner
От | Brian Hirt |
---|---|
Тема | Question about performance of planner |
Дата | |
Msg-id | 20001122110131.B21488@loopy.tr.berkhirt.com обсуждение исходный текст |
Ответы |
Re: Question about performance of planner
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
Hi, I have a question about the performance of the planner in 7.1. I've been testing the 11/21 snapshot of the database just to get an idea of how it will work for me when I upgrade from 7.02 I've noticed that some queries are taking much longer and I've narrowed it down (i think) to the planner. I've run an identical query against 7.02 and 7.1. Both databases have the exact same data, and both databases have been vacuum'd. As you can see from below, the 7.1 snapshot is spending 97% of the total time planning the query, where the 7.0.2 version is spending only 27% of the total time planning the query. If anyone is interested in this, I'll be happy to supply you with information that would help track this down. Thanks. 7.1-snapshot PLANNER STATISTICS ! system usage stats: ! 7.748602 elapsed 5.020000 user 0.200000 system sec ! [5.090000 user 0.210000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 47/1246 [349/1515] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 20 read, 0 written, buffer hit rate = 99.94% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written EXECUTOR STATISTICS ! system usage stats: ! 0.317000 elapsed 0.160000 user 0.010000 system sec ! [5.250000 user 0.220000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 328/364 [677/1879] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 160 read, 0 written, buffer hit rate = 97.73% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written 7.0.2 ! Planner Stats: ! system usage stats: ! 0.051438 elapsed 0.050000 user 0.000000 system sec ! [0.330000 user 0.050000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/51 [680/837] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written ! Executor Stats: ! system usage stats: ! 0.136506 elapsed 0.130000 user 0.000000 system sec ! [0.460000 user 0.050000 sys total] ! 0/0 [0/0] filesystem blocks in/out ! 0/6 [680/843] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent ! 0/0 [0/0] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 98 read, 0 written, buffer hit rate = 98.98% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written -- The world's most ambitious and comprehensive PC game database project. http://www.mobygames.com
В списке pgsql-hackers по дате отправления: