MemoryContextSwitchTo during table scan?

From: Jignesh Shah
Subject: MemoryContextSwitchTo during table scan?
Date: ,
Msg-id: BAY101-F302023446A2349317A516D0B60@phx.gbl
(view: Whole thread, Raw)
Responses: Re: MemoryContextSwitchTo during table scan?  (Tom Lane)
Re: MemoryContextSwitchTo during table scan?  (Josh Berkus)
Re: MemoryContextSwitchTo during table scan?  (Neil Conway)
List: pgsql-performance

Tree view

MemoryContextSwitchTo during table scan?  ("Jignesh Shah", )
 Re: MemoryContextSwitchTo during table scan?  (Tom Lane, )
  Re: MemoryContextSwitchTo during table scan?  ("Luke Lonergan", )
  Re: MemoryContextSwitchTo during table scan?  ("Jignesh Shah", )
 Re: MemoryContextSwitchTo during table scan?  (Josh Berkus, )
 Re: MemoryContextSwitchTo during table scan?  (Neil Conway, )

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




pgsql-performance by date:

From: Josh Berkus
Date:
Subject: Re: MemoryContextSwitchTo during table scan?
From: Tom Lane
Date:
Subject: Re: complex query performance assistance request