Re: [SQL] DELETE FROM takes forever
Even DB2 and Oracle will take hellishly long times to perform large scale deletes What I do for a database just under 300 gb in size is do deletes in groups of 10,000 So your where clause might look some like WHERE id NOT IN (SELECT id FROM unique_records fetch first 1 rows only) DB2 has a clause of "with ur" to specify its ok to use dirty reads. I am not sure if postgres has this, been awhile. The goal is to make sure postgres allows "dirty reads". It prevents row locking... In DB2 the query would like like: DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records fetch first 1 rows only) with ur Other tips that might enhance the performance is make sure the unique_records table is indexed... even if it has a primary key. In some cases the optimizer may choose an index to satisfy the select clause or it may do a table scan. Table scans are more costly than index scans. What's going to save you the real time is to break up your delete into chunks. All the rdbms log the transactions and each delete is a transaction. See where this is going? Some rdbms allow you to turn off "transactional logging" some don't. DB2 doesn't (( at least not without more effort than reasonably necessary )) so I write my delete queries to use chunks at a time. The most I would recommend is 100,000 records deleted at once. Play with timing and see what works for you Hope this helps -Rob -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Josh Sent: Thursday, February 10, 2011 11:57 AM To: pgsql-sql@postgresql.org Subject: [SQL] DELETE FROM takes forever Hi I'm trying to do a DELETE FROM on my large table (about 800 million rows) based on the contents of another, moderately large table (about 110 million rows). The command I'm using is: DELETE FROM records WHERE id NOT IN (SELECT id FROM unique_records); This process ran for about two weeks before I decided to stop it -- it was dragging down the DB server. I can understand long-running processes, but two weeks seems a bit much even for a big table. Is this the best way to approach the problem? Is there a better way? Some background: The server is version 8.3, running nothing but Pg. The 'records' table has 'id' as its primary key, and one other index on another column. The table is referenced by just about every other table in my DB (about 15 other tables) via foreign key constraints, which I don't want to break (which is why I'm not just recreating the table rather than deleting rows). Most of the dependent tables have ON DELETE CASCADE. The 'unique_records' table is a temp table I got via something like: SELECT DISTINCT (other_column) id INTO unique_records FROM records Thanks very much! Josh Leder -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmas...@argushealth.com. Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to construct sql
db2 has a group by rollup function.. does this exist in postgres? -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Oliveiros Sent: Wednesday, June 02, 2010 11:55 AM To: Wes James; pgsql-sql@postgresql.org Subject: Re: [SQL] how to construct sql Hi, Have you already tried this out? select MAX(page_count_count) - MIN(page_count_count) from page_count group by page_count_pdate. Best, Oliveiros - Original Message - From: "Wes James" To: Sent: Wednesday, June 02, 2010 5:48 PM Subject: [SQL] how to construct sql >I am grabbing a printer total and putting it in a table. The > page_count is continuously increasing: > > page_count_countpage_count_pdate > 10 2010-05-10 > 20 2010-05-10 > 40 2010-05-11 > 60 2010-05-11 > 80 2010-05-11 > 100 2010-05-12 > 120 2010-05-12 > . > > and so on. > > I can do: > > select sum(page_count_count) from page_count group by page_count_pdate. > > and get a total for a day. But this is not the total I want. I want > the total page count for the day. This would mean getting the first > page count of the day and then subtracting that from last page_count > for the day. For 2010-05-11 above it would be > > 80 - 40 = 40 total for the day. Is there a way to do this with sql? > > thx, > > -wes > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmas...@argushealth.com. Thank you. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL Developer accessing PostgreSQL
I recommend Aqua Data Studio Just drop in the jdbc jar From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Snyder, James Sent: Monday, March 29, 2010 11:34 AM To: pgsql-sql@postgresql.org Subject: [SQL] SQL Developer accessing PostgreSQL Hello, Is there a way to configure Oracle's SQL Developer to access a PostgreSQL database? Thanks,Jim PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmas...@argushealth.com. Thank you.
Re: [SQL] Week to date function
U only 52 calendar weeks in a year... I'm almost sure that is the
norm
-Original Message-
From: pgsql-sql-ow...@postgresql.org
[mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Ireneusz Pluta
Sent: Saturday, March 27, 2010 3:22 PM
To: Jorge Godoy
Cc: Sergey Konoplev; pgsql-sql@postgresql.org
Subject: Re: [SQL] Week to date function
Jorge Godoy pisze:
> Are you sure?
>
> http://en.wikipedia.org/wiki/ISO_8601
snip
>
> As Jan 1st, 2010 happened on a Friday, it was on week 53 of 2009.
you are right, thanks for pointing that out.
I didn't check the opposite way like this:
select date_part('week', '2010-01-01'::date);
date_part
---
53
I need to recheck my code.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information
intended only for the use of the individual or entity named above. If the
reader of the email is not the intended recipient or the employee or agent
responsible for delivering it to the intended recipient, you are hereby
notified that any use, dissemination or copying of this email transmission is
strictly prohibited by the sender. If you have received this transmission in
error, please delete the email and immediately notify the sender via the email
return address or mailto:postmas...@argushealth.com. Thank you.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).
I recommend switching to aqua data studio I can query mysql, postgres, db2, oracle with the same tool From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Dmitriy Igrishin Sent: Thursday, March 18, 2010 4:44 PM To: postgres list Subject: [SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL). Hello all Emacs users! I am using Emacs recently. I love sql-mode, to use with PostgreSQL, but I have problems with it. When my SQL file (or buffer) are small (50-100 lines) I can send it to SQLi buffer without any problems. But when I working with large SQL file (e.g. complex database model, thousands of lines) and send it to SQLi buffer it does not work properly. Something going on and in SQLi buffer (psql) I see incomplete SQL statement, for example: super=# super'# super'# super'# super'# It seems to single quote was not closed. But SQL is absolutely correct and loads fine when I load it from file directly from psql. I think, the problem with Emacs buffers... Please, help! And please sorry, for question not about PostgreSQL... Regards, Dmitriy Igrishin PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmas...@argushealth.com. Thank you.
