472,374 Members | 1,497 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

Problem analyzing performance of query

I have a query that is taking too long when run from a larger plpgsql function
(40-50s). However when I explain analyze it under psql it runs fine (4-5s).
This is with the same parameters, and I've even tried embedding the parameters
inside a subquery to avoid letting the planner see any more info than normal.

Is there any way to ask the server what plan it's using when it's actually
executing the query in production, rather than trying to feed it the same
query later in another context and hope it gets the same result?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?


Nov 23 '05 #1
2 1136
Greg Stark <gs*****@mit.edu> writes:
Is there any way to ask the server what plan it's using when it's actually
executing the query in production, rather than trying to feed it the same
query later in another context and hope it gets the same result?

From the planner's point of view, a plpgsql query involving plpgsql
variables is a parameterized query, which is the same as a PREPAREd
query with parameters. So for instance

create function foo(int) ...
select ... where keycol = $1;

looks the same as

PREPARE q(int) AS select ... where keycol = $1;

and you can investigate the plan for this with


Clear? It'd be nice to have more infrastructure for debugging plpgsql
code, but so far no one's got round to building any :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

Tom Lane <tg*@sss.pgh.pa.us> writes:
Clear? It'd be nice to have more infrastructure for debugging plpgsql
code, but so far no one's got round to building any :-(

Thank you. Very useful.

Though one of my worries was that there was some cached plan in the plpgsql.
What might be useful not just for plpgsql but for debugging other applications
would be a "trace" functionality like Oracle's. It could be as simple as a guc
variable which caused all queries to run under EXPLAIN ANALYZE and dumped the
results somewhere for later analysis.
As it happens I've narrowed the problem down and it wasn't related to any
planning at all. I was running EXPLAIN ANALYZE on only the SELECT portion of
the query but the full query was actually an "INSERT INTO foo () (SELECT ...)"
Apparently the INSERT side of it was taking 90% of the time.

Dropping a few gist indexes I was experimenting with have sped it up tenfold.

Now I'm wondering whether it's inevitable slow down of maintaining these
indexes or if something else was wrong. The gist indexes are not something I'm
using currently, but I may need them in the future to deal with larger

This table is a pre-generated "cache" of denormalized data. It's periodically
truncated and regenerated. Perhaps each time I truncated it the gist indexes
accumulated dead pages?

Most rows are quite small but there are a few large rows (large integer
arrays) perhaps they caused the gist indexes using gist__intbig_ops to have to
do lots of extra work? How do I tell if the rows are getting toasted?

I did run this under profiling and the gprof output didn't seem to be pointing
the finger at gist functions. A significant amount of time was spent in them
to be sure, but not 90%. That's what initially led me astray or I would have
checked this much earlier.

Flat profile:

Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls s/call s/call name
14.66 14.54 14.54 6327634 0.00 0.00 FunctionCall3
6.42 20.91 6.37 96792 0.00 0.00 gistchoose
4.43 25.31 4.40 26 0.17 0.17 ArrayGetNItems
3.59 28.87 3.56 450164 0.00 0.00 MemoryContextAllocZero
3.38 32.22 3.35 9505045 0.00 0.00 AllocSetFreeIndex
2.93 35.12 2.91 181680 0.00 0.00 ExecMakeFunctionResultNoSets
2.41 37.51 2.39 6431187 0.00 0.00 gistdentryinit
2.28 39.77 2.26 5320723 0.00 0.00 AllocSetAlloc
2.24 41.99 2.22 3294701 0.00 0.00 tas
2.12 44.09 2.10 671001 0.00 0.00 hash_search
1.71 45.79 1.70 1538836 0.00 0.00 _bt_compare
1.47 47.25 1.46 1098578 0.00 0.00 nocachegetattr
1.41 48.65 1.40 1478208 0.00 0.00 ExecEvalVar
1.40 50.04 1.39 597373 0.00 0.00 StrategyBufferLookup
1.29 51.31 1.28 769755 0.00 0.00 comparetup_heap
1.14 52.44 1.13 5548263 0.00 0.00 gistpenalty
1.11 53.55 1.11 28058 0.00 0.00 XLogInsert
1.08 54.62 1.07 6687057 0.00 0.00 FunctionCall1
1.06 55.67 1.05 641646 0.00 0.00 hash_any
1.06 56.72 1.05 53305 0.00 0.00 gistlayerinsert
1.04 57.75 1.04 475840 0.00 0.00 gistDeCompressAtt
1.04 58.78 1.03 991639 0.00 0.00 AllocSetReset
1.04 59.81 1.03 295968 0.00 0.00 ExecTargetList
[That's 60% of the time already]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Virginia | last post by:
We have a product that runs on Oracle. The version of Oracle I'm working with is 8.1.7. I should also note that I'm relatively new to Oracle. I'm troubleshooting one particular database that is...
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
by: Jason | last post by:
The following stored procedure is taking too long (in my opinion). The problem seems to be the SUM line. When commented out the query takes a second or two. When included the response time climbs...
by: hishamfangs | last post by:
Hi guys! I'm facing a problem and I can't quite figure it out! I have created a table on SQL Server 2000 to store call records. We get about 250,000 calls a day, and the most frequently used...
by: Uros | last post by:
When closing some forms in MSAccess 2003 Access is crashed (Microsoft Office Access has encounteres a problem and needs to close. We are sorry .......). If I close form without changes work OK, but...
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
by: JACompute | last post by:
I have an invoicing system that uses a VB6 program to post new invoice entries to a centralized Vendor management system (also in VB6), via a Web server and some ASP code. The system has been in...
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where...
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.