Re: [GENERAL] best way to query
Tom Lane wrote: Steve Clark [EMAIL PROTECTED] writes: explain shows: Aggregate (cost=4712921585.30..4712921585.31 rows=1 width=0) - Seq Scan on t_event_ack_log a (cost=103170.29..4712920878.60 rows=282677 width=0) Filter: (NOT (subplan)) SubPlan - Materialize (cost=103170.29..117301.92 rows=1016163 width=4) - Index Scan using pk_tuel_eln on t_unit_event_log (cost=0.00..98184.12 rows=1016163 width=4) Yeah, that's going to suck. A brute force solution is to see if you can get it to switch to a hashed subplan by increasing work_mem. Also, whatever is the ORDER BY for? regards, tom lane without the order by it wants to do a seq scan of t_unit_event_log. see below: explain select count(*) from t_event_ack_log where event_log_no not in (select event_log_no from t_unit_event_log); QUERY PLAN Aggregate (cost=12144872193.82..12144872193.82 rows=1 width=0) - Seq Scan on t_event_ack_log (cost=0.00..12144871485.07 rows=283497 width=0) Filter: (NOT (subplan)) SubPlan - Seq Scan on t_unit_event_log (cost=0.00..40286.56 rows=1021156 width=4) (5 rows) Will try increasing work_memory. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] best way to query
Daniel Verite wrote: Steve Clark wrote: any way i have 2 table - A and B. each table has a key field and if a row is in B it should have a corresponding row in A - but theres the problem it doesn't for all the rows in B. So I want to do something like delete from B where key not in (select key from A order by key); The problem is there are about 1,000,000 rows in A and 300,000 rows in B. I let the above run all night and it was still running the next morning. Does anyone have an idea of a better way. An outer join is sometimes spectacularly more efficient for this particular kind of query. I'd suggest you try: delete from B where key in (select B.key from B left outer join A on A.key=B.key where A.key is null) WOW! this runs in about 10 seconds - thanks Daniel. explain select count(*) from t_event_ack_log where event_log_no in (select t_event_ack_log.event_log_no from t_event_ack_log left outer join t_unit_event_log a on a.event_log_no=t_event_ack_log.event_log_no where a.event_log_no is null); QUERY PLAN Aggregate (cost=128349.56..128349.57 rows=1 width=0) - Hash Join (cost=94512.91..126935.36 rows=565681 width=0) Hash Cond: (public.t_event_ack_log.event_log_no = public.t_event_ack_log.event_log_no) - Seq Scan on t_event_ack_log (cost=0.00..14759.85 rows=565685 width=4) - Hash (cost=92609.85..92609.85 rows=152245 width=4) - HashAggregate (cost=91087.40..92609.85 rows=152245 width=4) - Hash Left Join (cost=57337.95..90380.29 rows=282842 width=4) Hash Cond: (public.t_event_ack_log.event_log_no = a.event_log_no) Filter: (a.event_log_no IS NULL) - Seq Scan on t_event_ack_log (cost=0.00..14759.85 rows=565685 width=4) - Hash (cost=40696.09..40696.09 rows=1014309 width=4) - Seq Scan on t_unit_event_log a (cost=0.00..40696.09 rows=1014309 width=4) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] best way to query
Hello List, this is a noobie question: I have had to take over an existing system - it was supposed to have some contraints that prevented dangling references - but... any way i have 2 table - A and B. each table has a key field and if a row is in B it should have a corresponding row in A - but theres the problem it doesn't for all the rows in B. So I want to do something like delete from B where key not in (select key from A order by key); The problem is there are about 1,000,000 rows in A and 300,000 rows in B. I let the above run all night and it was still running the next morning. Does anyone have an idea of a better way. B = t_event_ack_log A = t_unit_event_log explain shows: Aggregate (cost=4712921585.30..4712921585.31 rows=1 width=0) - Seq Scan on t_event_ack_log a (cost=103170.29..4712920878.60 rows=282677 width=0) Filter: (NOT (subplan)) SubPlan - Materialize (cost=103170.29..117301.92 rows=1016163 width=4) - Index Scan using pk_tuel_eln on t_unit_event_log (cost=0.00..98184.12 rows=1016163 width=4) OBTW: how do I interpret the cost - the manual says: planner's guess at how long it will take to run the statement (measured in units of disk page fetches) Not sure I understand (measured in units of disk page fetches) Thanks, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] best way to query
Tom Lane wrote: Steve Clark [EMAIL PROTECTED] writes: Tom Lane wrote: Also, whatever is the ORDER BY for? without the order by it wants to do a seq scan of t_unit_event_log. see below: explain select count(*) from t_event_ack_log where event_log_no not in (select event_log_no from t_unit_event_log); QUERY PLAN Aggregate (cost=12144872193.82..12144872193.82 rows=1 width=0) - Seq Scan on t_event_ack_log (cost=0.00..12144871485.07 rows=283497 width=0) Filter: (NOT (subplan)) SubPlan - Seq Scan on t_unit_event_log (cost=0.00..40286.56 rows=1021156 width=4) (5 rows) Hmm, the big problem with that isn't the seqscan but the lack of a Materialize step to buffer it; which says to me that you're running a pretty old Postgres version (8.0 or older). You should think about an update if you're running into performance issues. regards, tom lane Hi Tom, Actually this is show server_version; server_version 8.2.5 On FreeBSD 6.2 And Daniel gave me a query that does the job in just a few seconds. Thanks, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_dumpall
Erik Jones wrote: On Jan 17, 2008, at 1:08 PM, Greg Smith wrote: On Thu, 17 Jan 2008, Tom Lane wrote: There isn't any good way to guarantee time coherence of dumps across two databases. Whether there's a good way depends on what you're already doing. If you're going to the trouble of making a backup using PITR anyway, it's not hard to stop applying new logs to that replica and dump from it to get a point in time backup across all the databases. That's kind of painful now because you have to start the server to run pg_dumpall, so resuming recovery is difficult, but you can play filesystem tricks to make that easier. Actually, this exact scenario brings up a question I was thinking of last night. If you stop a PITR standby server and bring it up to dump from, will all of the database file have something written to them at some point during the dump? Transactional information is what I'd assume would be written, if so, but I'm not really sure of the low level details there. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com Thanks for everyone that replied to my query about pg_dumpall. Now another question/issue - anytime I usr createdb the resulting db ends up with UTF-8 encoding unless I use the -E switch. Is there a way to make the default be sql_ascii? postgres version is 8.2.5 Thanks again Steve ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 7.4.6 to 8.2.5 - ' changes to $_$
Bricklen Anderson wrote: Steve Clark wrote: function from 7.4.x postgres CREATE FUNCTION update_dns(text, text) RETURNS integer AS 'UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;' LANGUAGE sql; I load it into 8.2.5 - then dump it out and it is changed to CREATE FUNCTION update_dns(text, text) RETURNS integer AS $_$UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;$_$ LANGUAGE sql; notice $_$ where the single ' use to be. Is there some way to keep this from happening? The reason is we have systems in the field that have configuration information stored in 7.4.x. We want to upload that db info load it into an 8.2.5 db massage it then send it back to the unit in the field. I realize there are things I am going to have to fix up in the 8.2.5 dump to be able to load it back into the 7.4.x db but I want to minimize that as much as possible. We have some units in the field running 8.1.3 and it does not change the ' to $_$. Thanks, Steve I think --disable-dollar-quoting will work. (pg_dump --help) Thanks a lot. I missed that option in the man page - but now I see it. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] 7.4.6 to 8.2.5 - ' changes to $_$
function from 7.4.x postgres CREATE FUNCTION update_dns(text, text) RETURNS integer AS 'UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;' LANGUAGE sql; I load it into 8.2.5 - then dump it out and it is changed to CREATE FUNCTION update_dns(text, text) RETURNS integer AS $_$UPDATE domain_details SET domain = $2 WHERE domain = $1; DELETE from domains where domain = $1; SELECT 1 AS ignore;$_$ LANGUAGE sql; notice $_$ where the single ' use to be. Is there some way to keep this from happening? The reason is we have systems in the field that have configuration information stored in 7.4.x. We want to upload that db info load it into an 8.2.5 db massage it then send it back to the unit in the field. I realize there are things I am going to have to fix up in the 8.2.5 dump to be able to load it back into the 7.4.x db but I want to minimize that as much as possible. We have some units in the field running 8.1.3 and it does not change the ' to $_$. Thanks, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] losing db user password going from 7.4 to 8.2
Hello List, I am doing a pg_dumpall -c on 7.4. I then use psql to load into 8.2 everything seems to be right except my db user passwords don't work anymore. What am I missing. I have already tried starting 8.2 postgres with both #password_encryption = on password_encryption = off then reloading the database dump. Thanks, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] pg_dumpall
Hello List, the man page for pg_dump say: pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. does pg_dumpall make consistent backups if the database is being used concurrently? Even though the man page doesn't say it does. Thanks, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] losing db user password going from 7.4 to 8.2
Scott Marlowe wrote: On Jan 17, 2008 10:35 AM, Steve Clark [EMAIL PROTECTED] wrote: Hello List, I am doing a pg_dumpall -c on 7.4. I then use psql to load into 8.2 everything seems to be right except my db user passwords don't work anymore. What am I missing. What error message are you getting? Duh - my bad - my browser had the wrong password in it - so i thought things were broken. Sorry for the noise - as he hides his head in shame. Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
