Hello,
I am running PostgreSQL 8.0.x on Solaris 10 AMD64. My Tablesize for this
test is about 80G. When I run a query on a column which is not indexed, I
get a full table scan query and that's what I am testing right now. (I am
artificially creating that scenario to improve that corner case). Aparently
I find that the full query is running much slower compared to what hardware
can support and hence dug into DTrace to figure out where it is spending
most of its time.
Running a script (available on my blog) I find the following top 5 functions
where it spends most time during a 10 second run of the script
<PRE>
Time in (millisec)
Call Count
MemoryContextSwitchTo 775 106564
LockBuffer 707
109367
LWLockAcquire 440
58888
ExecEvalConst 418
53282
ResourceOwnerRememberBuffer 400 54684
TransactionIdFollowsOrEquals 392
53281
</PRE>
While the times look pretty small (0.775 second out of 10seconds which is
about 7.75%), it still represents significant time since the table is pretty
big and the entire scan takes about 30 minute (about 80G big table).
Considering it is a single threaded single process scan all the hits of the
function calls itself can delay the performance.
MemoryContextSwitchTo and LockBuffer itself takes 15% of the total time of
the query. I was expecting "read" to be the slowest part (biggest component)
but it was way down in the 0.4% level.
Now the question is why there are so many calls to MemoryContextSwitchTo in
a single SELECT query command? Can it be minimized?
Also is there any way to optimize LockBuffer?
Is there anything else that can minimize the time spent in these calls
itself? (Of course it is the first iteration but something else will be the
bottleneck... but that's the goal).
If there are any hackers interested in tackling this problem let me know.
Thanks.
Regards,
Jignesh
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
