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
