Re: [SQL] help on a query
Just curious, what is wrong with the first way of coding the solution? --( Forwarded letter 1 follows )- Date: Fri, 8 Oct 2004 08:44:23 +0400 To: Thomas.F.O'[EMAIL PROTECTED], [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Subject: Re: [SQL] help on a query On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote: > A query that should get the job done is: > > SELECT registration_id > FROM registrations r > WHERE NOT EXISTS ( > SELECT 1 > FROM receipts > WHERE registration_id = r.registration_id > ); Don't, PLEASE, don't !!! drive this way : SELECT r.registration_id FROM registrations AS r LEFT OUTER JOIN receipts AS rec ON rec.registration_id = r.registration_id WHERE rec.registration_id IS NULL; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] help on a query
O CHRIS HOOVER έγραψε στις Oct 8, 2004 : > Just curious, what is wrong with the first way of coding the solution? > --( Forwarded letter 1 follows )- > Date: Fri, 8 Oct 2004 08:44:23 +0400 > To: Thomas.F.O'[EMAIL PROTECTED], [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > From: [EMAIL PROTECTED] > Sender: [EMAIL PROTECTED] > Subject: Re: [SQL] help on a query > > On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote: > > A query that should get the job done is: > > > > SELECT registration_id > > FROM registrations r > > WHERE NOT EXISTS ( > > SELECT 1 > > FROM receipts > > WHERE registration_id = r.registration_id > > ); > > Don't, PLEASE, don't !!! > > drive this way : > > SELECT r.registration_id > FROM registrations AS r > LEFT OUTER JOIN receipts AS rec > ON rec.registration_id = r.registration_id > WHERE rec.registration_id IS NULL; Some one could add the same dont please dont, and advise: select registration_id FROM registrations where registration_id not in (select registration_id from receipts); but its actually version/data dependent i suppose. > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] help on a query
I think the OUTER JOIN version is probably more efficient, but EXPLAIN would tell you. -tfo On Oct 8, 2004, at 8:02 AM, CHRIS HOOVER wrote: Just curious, what is wrong with the first way of coding the solution? --( Forwarded letter 1 follows )- Date: Fri, 8 Oct 2004 08:44:23 +0400 To: Thomas.F.O'[EMAIL PROTECTED], [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Subject: Re: [SQL] help on a query On Friday 08 October 2004 07:10, Thomas F.O'Connell wrote: A query that should get the job done is: SELECT registration_id FROM registrations r WHERE NOT EXISTS ( SELECT 1 FROM receipts WHERE registration_id = r.registration_id ); Don't, PLEASE, don't !!! drive this way : SELECT r.registration_id FROM registrations AS r LEFT OUTER JOIN receipts AS rec ON rec.registration_id = r.registration_id WHERE rec.registration_id IS NULL; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] help on a query
Thomas F.O'Connell wrote: I think the OUTER JOIN version is probably more efficient, but EXPLAIN would tell you. Well, this all makes me feel better. For everyone's edification: select registration_id FROM registrations where registration_id not in (select registration_id from receipts); Generates 0 rows The OUTER JOIN version is quite a bit more efficient (by an order of magnitude) than the option with WHERE NOT EXISTS subquery. Thanks all for the help. Saved my butt again!! -- .Michelle -- Michelle Murrain mmurrain at dbdes dot com 413-222-6350 ph 617-889-0929 ph 952-674-7253 fax <--- new Page: [EMAIL PROTECTED] AIM:pearlbear0 ICQ:129250575 Skype: pearlbear Jabber: [EMAIL PROTECTED] "I see all the different religious traditions as paths for the development of inner peace, which is the true foundation of world peace. These ancient traditions come to us as a gift from our common past. Will we continue to cherish it as a gift and hand it over to the future generations as a legacy of our shared desire for peace?" - His Holiness the Dalai Lama ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Record Count
Friends, How to get a the records returned by a sql executed within a pl/pgsql function Sreejith ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Record Count
On Fri, Oct 08, 2004 at 11:21:25PM +0530, sreejith s wrote: > How to get a the records returned by a sql executed within a pl/pgsql function It's not clear what you're asking, but perhaps one of the following pages will answer your question: http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-SELECT-INTO http://www.postgresql.org/docs/7.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS http://www.postgresql.org/docs/7.4/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING If you still have trouble, please post more info about what you're trying to do. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] help on a query
Michelle Murrain <[EMAIL PROTECTED]> writes: > The OUTER JOIN version is quite a bit more efficient (by an order of magnitude) > than the option with WHERE NOT EXISTS subquery. This is going to be heavily dependent on the version of postgres. IN/NOT IN execution has improved a lot in 7.4 and later. If you're still on 7.3 then the outer join will be better. But in 7.4 I would actually expect the NOT IN or the NOT EXISTS to be faster. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Impact of foreign keys on a simple count(*) ?
Hey folks (long email, my apologies),
I am wrapping up my schema upgrade, and I just noticed a real
show-stopper for me... Here is the "before" table structure:
email_id | integer | not null
joejob| boolean | default false
bayes_poison | boolean | default false
perm_error| boolean | default false
bait_id | integer | default 0
num_recip | integer | default 1
product | integer | default 1043
date_received | timestamp with time zone|
only_date | date|
only_time | time with time zone |
maildir_file | character varying(64) |
errors_to | character varying(512) |
reply_to | character varying(512) |
spammer | character varying(512) |
return_path | character varying(512) |
received_from | character varying(512) |
message_id| character varying(512) |
target_domain | character varying(512) |
mail_date | character varying(512) |
x_priority| character varying(512) |
x_msmail_priority | character varying(512) |
x_mimeole | character varying(512) |
mime_version | character varying(512) |
subject | character varying(1024) |
mail_to | character varying(2048) |
x_mailer | character varying(2048) |
content_type | character varying(2048) |
user_agent| character varying(2048) |
cc| character varying(2048) |
comments | character varying(8192) |
last_mod | timestamp without time zone | default
('now'::text)::timest
amp(6) with time zone
Indexes:
"emails_pkey" primary key, btree (email_id)
"emails_idx_bait_id" btree (bait_id)
"emails_idx_mail_to" btree (mail_to)
"emails_idx_only_date" btree (only_date)
"emails_idx_only_time" btree (only_time)
"emails_idx_product" btree (product)
"emails_idx_received_from" btree (received_from)
"emails_idx_subject" btree (subject)
"emails_idx_target_domain" btree (target_domain)
And here is the "after" structure:
email_id | integer | not null
joejob| boolean | default false
bayes_poison | boolean | default false
perm_error| boolean | default false
num_recip | integer | default 1
mydom_id | integer | default 0
spamv_id | integer | default 1053
spammer_id| integer | default 1003
last_mod | timestamp with time zone | default
('now'::text)::timestamp
with time zone
bait_id | integer |
product_id| integer |
date_received | timestamp with time zone |
only_date | date |
only_time | time with time zone |
maildir_file | character varying(128) |
x_priority| character varying(128) |
x_msmail_priority | character varying(128) |
x_mimeole | character varying(128) |
mime_version | character varying(512) |
received_from | character varying(512) |
content_type | character varying(512) |
errors_to | character varying(512) |
user_agent| character varying(512) |
mail_date | character varying(512) |
x_mailer | character varying(512) |
return_path | character varying(512) |
message_id| character varying(512) |
reply_to | character varying(512) |
subject | character varying(1024) |
mail_to | character varying(1024) |
cc| character varying(2048) |
Indexes:
"emails_pkey" primary key, btree (email_id)
"emails_maildir_file_key" unique, btree (maildir_file)
Foreign-key constraints:
"$1" FOREIGN KEY (bait_id) REFERENCES bait(bait_id) ON UPDATE CASCADE ON
DELETE SET NULL
"$2" FOREIGN KEY (product_id) REFERENCES products(product_id) ON UPDATE
CASCADE ON DELETE SET NULL
"$3" FOREIGN KEY (mydom_id) REFERENCES my_domains(mydom_id) ON UPDATE
CASCADE ON DELETE SET NULL
"$4" FOREIGN KEY (spamv_id) REFERENCES spamvertisers(spamv_id) ON UPDATE
CASCADE ON DELETE SET NULL
"$5" FOREIGN KEY (spammer_id) REFERENCES spammers(spammer_id) ON UPDATE
CASCADE ON DELETE SET NULL
I am absolutely aware of the fact that the new schema can still use
some work. :) But, it's a step in the right direction as I muddle my
way through this. Not to mention, I really did need those foreign keys.
The problem is this:
Original schema:
prod01=> EXPLAIN ANALYZE SELECT count(*) FROM emails;
QUERY PLAN
-
Re: [SQL] Impact of foreign keys on a simple count(*) ?
"C. Bensend" <[EMAIL PROTECTED]> writes: >Is it the addition of the multiple foreign keys that is slowing this > down so much? Foreign keys have zero, nada, zilch to do with the performance of count(*). The only plausible theory I can think of for the performance difference is that in your "new" database the table has been through several mass updates, leading to a whole lot of dead rows and a much larger physical table size to scan through. I'd suggest a VACUUM VERBOSE on both old and new copies of the table to get an idea of the relative physical sizes. You might need a VACUUM FULL to get the new table back down to a reasonable size... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Impact of foreign keys on a simple count(*) ?
> Foreign keys have zero, nada, zilch to do with the performance of > count(*). OK, I just wanted to make sure. > The only plausible theory I can think of for the performance > difference is that in your "new" database the table has been through > several mass updates, leading to a whole lot of dead rows and a much > larger physical table size to scan through. I'd suggest a VACUUM > VERBOSE on both old and new copies of the table to get an idea of the > relative physical sizes. You might need a VACUUM FULL to get the new > table back down to a reasonable size... Oh good lord. *waves the Idiot Flag as he slinks back under his rock* Thank you, Tom, for your patience. That was it. I'm a potatohead, but I'm learning. :) Benny, swearing he DID do a vacuum, but obviously not... -- "Even if a man chops off your hand with a sword, you still have two nice, sharp bones to stick in his eyes." -- .sig on Slashdot ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
