473,847 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

50K record DELETE Begins, 100% CPU, Never Completes 1 hour later

Again, we have an odd performance problem with PGSQL, 7.4b2.

Here is the query:

delete from numplan where pkid in
(select numplan.pkid from numplan
left outer join pilothuntgroup on numplan.pkid=pi lothuntgroup.fk numplan
left outer join devicenumplanma p on numplan.pkid = devicenumplanma p.fknumplan
where numplan.tkpatte rnusage=2
and pilothuntgroup. fknumplan is null
and devicenumplanma p.fknumplan is null);

The query starts, PGSQL shoots to 134MB(!) of memory and 100% CPU and nevercompletes.

The query works fine on smaller datasets. This occurs when 50K+ records exist in the numplan table.

Here is the query plan:

ccm=# explain delete from numplan where pkid in (select numplan.pkid fromnumplan left outer join pilothuntgroup on numplan.pkid=pi lothuntgroup.fk numplan left outer join devicenumplanma p on numplan.pkid = devicenumplanma p.fknumplan where numplan.tkpatte rnusage=2 and pilothuntgroup. fknumplan is null and devicenumplanma p.fknumplan is null);
QUERY PLAN
------------------------------------------------------------------------------------------------
Merge IN Join (cost=37947.25. .40851.71 rows=82225 width=6)
Merge Cond: ("outer"."?colu mn3?" = ("inner".pkid): :text)
-> Sort (cost=11481.65. .11687.35 rows=82279 width=46)
Sort Key: (public.numplan .pkid)::text
-> Seq Scan on numplan (cost=0.00..293 6.79 rows=82279 width=46)
-> Materialize (cost=26465.60. .27930.85 rows=82225 width=40)
-> Merge Left Join (cost=23917.22. .25822.60 rows=82225 width=40)
Merge Cond: (("outer".pkid) ::text = "inner"."?colum n2?")
Filter: ("inner".fknump lan IS NULL)
-> Merge Left Join (cost=11407.97. .11819.13 rows=82225width =40)
Merge Cond: ("outer"."?colu mn2?" = "inner"."?colum n2?")
Filter: ("inner".fknump lan IS NULL)
-> Sort (cost=11406.89. .11612.45 rows=82225 width=40)
Sort Key: (public.numplan .pkid)::text
-> Seq Scan on numplan (cost=0.00..314 2.49 rows=82225 width=40)
Filter: (tkpatternusage = 2)
-> Sort (cost=1.08..1.0 9 rows=4 width=42)
Sort Key: (pilothuntgroup .fknumplan)::te xt
-> Seq Scan on pilothuntgroup (cost=0.00..1.0 4 rows=4 width=42)
-> Sort (cost=12509.25. .12734.70 rows=90180 width=40)
Sort Key: (devicenumplanm ap.fknumplan):: text
-> Seq Scan on devicenumplanma p (cost=0.00..332 6.80 rows=90180 width=40)
(22 rows)

---
Clay
Cisco Systems, Inc.
cl*****@cisco.c om
(972) 813-5004
I've stopped 19,658 spam messages. You can too!
One month FREE spam protection at http://www.cloudmark.c om/spamnetsig/}

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

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #1
2 2230
"Clay Luther" <cl*****@cisco. com> writes:
ccm=# explain delete from numplan where pkid in (select numplan.pkid from numplan left outer join pilothuntgroup on numplan.pkid=pi lothuntgroup.fk numplan left outer join devicenumplanma p on numplan.pkid = devicenumplanma p.fknumplan where numplan.tkpatte rnusage=2 and pilothuntgroup. fknumplan is null and devicenumplanma p.fknumplan is null);


The left join/is null thingies look like a workaround for our pre-7.4
lack of performance with NOT IN queries. Have you tried expressing
this more straightforward ly with NOT IN?

Also, what sort_mem setting are you using?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #2
"Clay Luther" <cl*****@cisco. com> writes:
ccm=# explain delete from numplan where pkid in (select numplan.pkid from numplan left outer join pilothuntgroup on numplan.pkid=pi lothuntgroup.fk numplan left outer join devicenumplanma p on numplan.pkid = devicenumplanma p.fknumplan where numplan.tkpatte rnusage=2 and pilothuntgroup. fknumplan is null and devicenumplanma p.fknumplan is null);


The left join/is null thingies look like a workaround for our pre-7.4
lack of performance with NOT IN queries. Have you tried expressing
this more straightforward ly with NOT IN?

Also, what sort_mem setting are you using?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #3

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

Similar topics

0
434
by: Clay Luther | last post by:
Again, we have an odd performance problem with PGSQL, 7.4b2. Here is the query: delete from numplan where pkid in (select numplan.pkid from numplan left outer join pilothuntgroup on numplan.pkid=pilothuntgroup.fknumplan left outer join devicenumplanmap on numplan.pkid = devicenumplanmap.fknumplan where numplan.tkpatternusage=2 and pilothuntgroup.fknumplan is null
3
1555
by: Prakash | last post by:
Below is my code to delete a record in a continuous form. I can't figure out any reason but sometimes (another) record gets deleted instead of where the record pointer is positioned. Small table - just 750 records ... no primary key defined. Using Access 2003 under WinXP SP1. There must be something I've overlooked. Maybe one of you gurus could help.
20
2158
by: Bryan | last post by:
hello all... im trying to add a record to an sql db on ms sql server 2000, using vb.net. seems to be working.. except for one thing, one of the columns in the database is a bit datatype, and though i get no syntax errors when compiling, i get an error indicated that the data would be truncated. the field is login_status. ive tried in quotes and not, giving it an integer variable with the number 1
14
1572
by: David Garamond | last post by:
begin; update t set val=val+1; -- 1000 times commit; How many record versions does it create? 1 or 1000? I'm implementing a banner counter which is incremented at least 2-3 millions a day. I thought I'd cheat by only commiting after every few minutes. Would that work or would I still create as many record versions? --
6
3866
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called "Generations"), and it allows the user to add, edit and delete the various records of the table. "Generations" table has the following fields: "IDPerson", NamePerson", "AgePerson" and "IDParent". A record contains the information about a person (his name, his...
0
4118
by: hagar | last post by:
Hi all, I have a problem which I can not understand why this is happening! Debugging this I actually see that it grabs first record then when stepping through code to the line rsImportTo.AddNew it drops first record and grabs second record and continues on no problems (but no 1st record in data set) I am reading a text file record 1 is a top of text file. see code below Private Sub CmdFetchNewData_Click() on Error Goto CmdfetchErr Dim...
15
9946
by: batman | last post by:
i have a text file that is like: date = OCT0606 asdf sdaf asdfasdgsdgh asdfsdfasdg START-OF-DATA asdfasdfg asdfgdfgsfg
10
12725
by: MLH | last post by:
Suppose, in a multi-user environment, you have append query SQL in a VBA procedure that looks like INSERT INTO MyTable... and the next line reads MyVar=DMax("","MyTable... You can never be certain that MyVar will be set to the key-field value that was created when the Append query ran. Now, there are other ways to do it - I know - that will ensure you 'nab' the correct record. But I was wondering
19
4040
by: emanning | last post by:
Using Access 2003 and using a form that's mostly bound. I need a way to tell if user-1 is on the record when user-2 tries to open the same record, w/o waiting for the user-1 to save the record first. The only way I can think of is by adding a flag to the record and setting it to true when user-1 opens the record. If user-2 tries to open it, the flag is checked for true, then a message would be displayed to them that user-1 is on the...
8
2987
by: Michel Esber | last post by:
Hello, Env: DB2 V8 LUW FP16 running Linux create Table X (machine_id varchar(24) not null, ctime timestamp not null); create index iFoo on X (MACHINE_ID, CTIME) allow reverse scans; alter table X add primary key (MACHINE_ID, CTIME); Our C++ application inserts data into a table X using CLI array insert
0
9890
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10990
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10652
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10346
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9489
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7887
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7060
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4537
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4126
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.