Re: [GENERAL] Query too slow with "not in" condition
On Sun, Nov 30, 2008 at 3:28 PM, David Rowley <[EMAIL PROTECTED]> wrote: > You might find this page interesting: > http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/ > > Thanks David. Another issue I was faced with was exactly what the link you provided discusses. Best regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com
Re: [GENERAL] Query too slow with "not in" condition [Resolved]
Thanks a lot. On Sun, Nov 30, 2008 at 11:28 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "David Rowley" <[EMAIL PROTECTED]> writes: > > I assume workmem, effective_cache_size and random_page_cost are all the > same > > in the 2 postgresql.conf? > > Indeed, work_mem is probably the problem. The critical difference > between the two plans seems to be that the first one is using a > "hashed subplan" and the second one isn't. Assuming the same datatypes in > both databases, the only reason not to use a hashed subplan is if the > hashtable is estimated not to fit in work_mem. > I changed work_mem in test machine to be implemented in production server later. Completely forgot that when faced with this issue. Sorry for the trouble. I learnt about "hashed subplan" requiring sufficient work_mem, however. Thanks and regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Adrian Klaver a écrit : > On Friday 28 November 2008 3:47:10 am Stéphane A. Schildknecht wrote: >> drop table commande cascade; >> drop table commandeligne; >> >> CREATE TABLE commande >> ( >> id integer NOT NULL, >> montant real, >> CONSTRAINT id PRIMARY KEY (id) >> )with oids; >> >> CREATE TABLE commandeligne >> ( >> id_commande integer NOT NULL references commande (id) >> -- on delete cascade on update cascade >> , >> montant real, >> id_produit integer NOT NULL, >> CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) >> )with oids; >> >> CREATE OR REPLACE FUNCTION p_commande_bd() RETURNS "trigger" AS >> $BODY$ >> BEGIN >> -- RAISE NOTICE 'Table commandeligne : suppression de la ligne %', >> OLD.id; DELETE FROM commandeligne WHERE id_commande = OLD.id; >> -- RAISE NOTICE 'Table commandeligne : ligne % supprimée', OLD.id; >> RETURN OLD; >> END; >> $BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> >> DROP TRIGGER IF EXISTSp_commande_bd ON commande; >> CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE >> PROCEDURE p_commande_bd(); >> >> CREATE OR REPLACE FUNCTION p_commandeligne_ad() RETURNS "trigger" AS >> $BODY$ >> BEGIN >> -- RAISE NOTICE 'Table commande : maj de la ligne %', OLD.id_commande; >> UPDATE commande SET montant=montant-OLD.montant WHERE id = >> OLD.id_commande; -- RAISE NOTICE 'Table commande : ligne % maj (%)', >> OLD.id_commande, OLD.montant; >> RETURN NEW; >> END; >> $BODY$ >> LANGUAGE 'plpgsql' VOLATILE; >> DROP TRIGGER IF EXISTSp_commandeligne_ad ON commandeligne; >> CREATE TRIGGER p_commandeligne_ad AFTER DELETE ON commandeligne FOR EACH >> ROW EXECUTE PROCEDURE p_commandeligne_ad(); >> >> >> First step : Creating first command >> insert into commande(id, montant) values(1,150); >> insert into commandeligne(id_commande,id_produit, montant) values(1,1,100); >> insert into commandeligne(id_commande,id_produit, montant) values(1,2,20); >> insert into commandeligne(id_commande,id_produit, montant) values(1,3,30); >> >> select oid,* from commande where id=1; >> select oid,* from commandeligne where id_commande=1; >> >> 2nd step : Deletion of command 1 >> delete from commande where id=1; > > When I run this test case I get: > > test=# 2nd step : Deletion of command 1 > test=# delete from commande where id=1; > ERROR: update or delete on table "commande" violates foreign key > constraint "commandeligne_id_commande_fkey" on table "commandeligne" > DETAIL: Key (id)=(1) is still referenced from table "commandeligne". > > The FK in commandeligne (id_commande integer NOT NULL references commande > (id)) is preventing the trigger from completing. > Here, I don't get that error. Maybe you could try creating the commandeligne table like that : CREATE TABLE commandeligne ( id_commande integer NOT NULL -- references commande (id) -- on delete cascade on update cascade , montant real, id_produit integer NOT NULL, CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) )with oids; I'm running PG 8.3.5 or 8.2.11, result is the same. Regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr Dalibo - http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJM4+zA+REPKWGI0ERAmeCAKCV5upN9r7174fzIQRLE6pajSc1tACg4pw0 SRmXwnN3huC4A6vteOo9CkE= =mlSt -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on libpq parameters
At 1:26 PM -0500 11/30/08, Tom Lane wrote: Owen Hartnett <[EMAIL PROTECTED]> writes: Yes, it did. I'm confused. My first parameter is a string, but the following two are integers. I thought the paramType parameter indicated the type. Do the integers need to be sprintf'd to strings? Yes. Alternatively, you could pass the integers as binary, but that's not notation-free either (you need htonl or some such). regards, tom lane Thanks, that did it. I got confused with the binary parameters, and PQgetvalue returning the binary through a char * when it returns a binary value. -Owen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
Andrus wrote: There are no console users in this server other than root. /home directory is empty. console access is used only some times in year. Andrus, I don't know how this conversation ended up on a PG mailing list, but Gentoo maintains many user forums where people are happy to answer questions about that distro: http://www.gentoo.org -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
On Sun, Nov 30, 2008 at 3:16 PM, Andrus <[EMAIL PROTECTED]> wrote: > Scott, > >> emerge lftp > > root # emerge lftp > Calculating dependencies... done! > Emerging (1 of 1) net-ftp/lftp-3.4.6 to / Downloading 'http://distfiles.gentoo.org/distfiles/lftp-3.4.6.tar.gz' > > --23:39:28-- http://distfiles.gentoo.org/distfiles/lftp-3.4.6.tar.gz > => `/usr/portage/distfiles/lftp-3.4.6.tar.gz' > Lahendan distfiles.gentoo.org... 64.50.236.52, 64.50.238.52, > 140.211.166.134, ... > Loon C¼hendust serveriga distfiles.gentoo.org|64.50.236.52|:80... C¼hendus > loodud. > HTTP pC¤ring saadetud, ootan vastust... 404 Not Found > 23:39:29 VIGA 404: Not Found. > Downloading 'http://distro.ibiblio.org/pub/linux/distributions/gentoo/distfiles/lftp-3.4.6.tar.gz' > > --23:39:29-- > > http://distro.ibiblio.org/pub/linux/distributions/gentoo/distfiles/lftp-3.4.6.tar.gz > => `/usr/portage/distfiles/lftp-3.4.6.tar.gz' > Lahendan distro.ibiblio.org... 152.46.7.109 > Loon C¼hendust serveriga distro.ibiblio.org|152.46.7.109|:80... C¼hendus > loodud. > HTTP pC¤ring saadetud, ootan vastust... 404 Not Found > 23:39:31 VIGA 404: Not Found. > Downloading 'ftp://lftp.yar.ru/lftp/old/lftp-3.4.6.tar.gz' > > --23:39:31-- ftp://lftp.yar.ru/lftp/old/lftp-3.4.6.tar.gz > => `/usr/portage/distfiles/lftp-3.4.6.tar.gz' > Lahendan lftp.yar.ru... 193.233.48.99 > Loon C¼hendust serveriga lftp.yar.ru|193.233.48.99|:21... ebaCµnnestus: > Connection refused. > !!! Couldn't download 'lftp-3.4.6.tar.gz'. Aborting. Sounds like your install of gentoo is broken somehow. I'd check a gentoo support forum for help, or wait and see if someone here knows it well enough to help you out. >> Because ftp is running as a cron? Doesn't have the right permissions? > > I ran script from command line as root. > Running from cron does not also upload copy. since I used cron -e as root, > script runs probably in cron also as root. Don't do that. Create an extra account on both machines with a password set to something you know and use that. If they're both unix / linux boxes look into using rsync, which is much more stable, just setup a set of ssh keys on the machine that's running rsync and put them in authorized_keys file on the destination. google it, there's lots of howtos on that. >> Because it appeared the username and password were failing? >> Maybe a lack of EOF in the >> original example you posted. Could be a lot of reasons. > > This script tries to write to /root/my-backup directory in ftp server which > fails. > I fixed this and now it works. > There is no EOF in script but it still works. OK, find out how to create users on your gentoo server, and see if you can figure out what's broken in emerge so you can install lftp. It's much better than regular ftp, as it has assured transfer completion and other useful features if you're interested in making sure the file gets transferred. Or switch to rsync, which is how I do it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
Scott, emerge lftp root # emerge lftp Calculating dependencies... done! Emerging (1 of 1) net-ftp/lftp-3.4.6 to / Downloading 'http://distfiles.gentoo.org/distfiles/lftp-3.4.6.tar.gz' --23:39:28-- http://distfiles.gentoo.org/distfiles/lftp-3.4.6.tar.gz => `/usr/portage/distfiles/lftp-3.4.6.tar.gz' Lahendan distfiles.gentoo.org... 64.50.236.52, 64.50.238.52, 140.211.166.134, ... Loon C¼hendust serveriga distfiles.gentoo.org|64.50.236.52|:80... C¼hendus loodud. HTTP pC¤ring saadetud, ootan vastust... 404 Not Found 23:39:29 VIGA 404: Not Found. Downloading 'http://distro.ibiblio.org/pub/linux/distributions/gentoo/distfiles/lftp-3.4.6.tar.gz' --23:39:29-- http://distro.ibiblio.org/pub/linux/distributions/gentoo/distfiles/lftp-3.4.6.tar.gz => `/usr/portage/distfiles/lftp-3.4.6.tar.gz' Lahendan distro.ibiblio.org... 152.46.7.109 Loon C¼hendust serveriga distro.ibiblio.org|152.46.7.109|:80... C¼hendus loodud. HTTP pC¤ring saadetud, ootan vastust... 404 Not Found 23:39:31 VIGA 404: Not Found. Downloading 'ftp://lftp.yar.ru/lftp/old/lftp-3.4.6.tar.gz' --23:39:31-- ftp://lftp.yar.ru/lftp/old/lftp-3.4.6.tar.gz => `/usr/portage/distfiles/lftp-3.4.6.tar.gz' Lahendan lftp.yar.ru... 193.233.48.99 Loon C¼hendust serveriga lftp.yar.ru|193.233.48.99|:21... ebaCµnnestus: Connection refused. !!! Couldn't download 'lftp-3.4.6.tar.gz'. Aborting. Because ftp is running as a cron? Doesn't have the right permissions? I ran script from command line as root. Running from cron does not also upload copy. since I used cron -e as root, script runs probably in cron also as root. Because it appeared the username and password were failing? Maybe a lack of EOF in the original example you posted. Could be a lot of reasons. This script tries to write to /root/my-backup directory in ftp server which fails. I fixed this and now it works. There is no EOF in script but it still works. Thank you very much for quick help, it was really useful! What? Only root can access /root directory. Or do you mean something else? Only things that should be run by root are things that genuinely need to, and this doesn't look like one of them. Looks like you're trying to write to the /root directory. Don't do that. There are no console users in this server other than root. /home directory is empty. console access is used only some times in year. Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
Scott Marlowe wrote: On Sun, Nov 30, 2008 at 1:18 PM, Andrus <[EMAIL PROTECTED]> wrote: Sorry, I'm no gentoo expert. It's certainly not the first distro I'd recommend to someone just starting out with linux / unix. Time to break out the google. I also found quite a few example scripts for lftp with google in a few minutes. I must manage existing gentoo server where previous maintainer has left company. lftp is not found in gentoo and nobody knows how to activate it. So I must use existing ftp. It sounds like the last guy there was no gentoo expert either. Apparently the install command is something like emerge lftp sudo emerge lftp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
On Sun, Nov 30, 2008 at 1:18 PM, Andrus <[EMAIL PROTECTED]> wrote: >> Sorry, I'm no gentoo expert. It's certainly not the first distro I'd >> recommend to someone just starting out with linux / unix. Time to >> break out the google. I also found quite a few example scripts for >> lftp with google in a few minutes. > > I must manage existing gentoo server where previous maintainer has left > company. > lftp is not found in gentoo and nobody knows how to activate it. So I must > use existing ftp. It sounds like the last guy there was no gentoo expert either. Apparently the install command is something like emerge lftp but I can't confirm that, since I'm not sitting on a gentoo box. > This issue may not realted to ftp program. > I verified multiple times that file exists and is readable. > Any ides why ftp does not find /root/my-backups/mybackup6.backup ? There Because ftp is running as a cron? Doesn't have the right permissions? Because it appeared the username and password were failing? Because ftp wasn't designed to be scriptable? Maybe a lack of EOF in the original example you posted. Could be a lot of reasons. > must be something simple. > Can /root directoy access from fpt program is disabled for some strange > reason ? What? Only root can access /root directory. Or do you mean something else? Only things that should be run by root are things that genuinely need to, and this doesn't look like one of them. > [/root/my-backups/mybackup6.backup] Looks like you're trying to write to the /root directory. Don't do that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
Sorry, I'm no gentoo expert. It's certainly not the first distro I'd recommend to someone just starting out with linux / unix. Time to break out the google. I also found quite a few example scripts for lftp with google in a few minutes. I must manage existing gentoo server where previous maintainer has left company. lftp is not found in gentoo and nobody knows how to activate it. So I must use existing ftp. This issue may not realted to ftp program. I verified multiple times that file exists and is readable. Any ides why ftp does not find /root/my-backups/mybackup6.backup ? There must be something simple. Can /root directoy access from fpt program is disabled for some strange reason ? ftp server log contains (ip and names changed): 23:50:20 (2008-11-29) z12.zone.com [ftp.debug] pure-ftpd: ([EMAIL PROTECTED]) [DEBUG] Command [user] [d11551f21768] 23:50:20 (2008-11-29) z12.zone.com [ftp.debug] pure-ftpd: ([EMAIL PROTECTED]) [DEBUG] Command [pwd] [] 23:50:20 (2008-11-29) z12.zone.com [ftp.debug] pure-ftpd: ([EMAIL PROTECTED]) [DEBUG] Command [type] [I] 23:50:20 (2008-11-29) z12.zone.com [ftp.debug] pure-ftpd: ([EMAIL PROTECTED]) [DEBUG] Command [pasv] [] 23:50:20 (2008-11-29) z12.zone.com [ftp.debug] pure-ftpd: ([EMAIL PROTECTED]) [DEBUG] Command [stor] [/root/my-backups/mybackup6.backup] 23:50:20 (2008-11-29) z12.zone.com [ftp.err] pure-ftpd: ([EMAIL PROTECTED]) [ERROR] Can\'t open that file: No such file or directory 23:50:20 (2008-11-29) z12.zone.com [ftp.err] pure-ftpd: ([EMAIL PROTECTED]) [ERROR] Rename/move failure: No such file or directory 23:50:20 (2008-11-29) z12.zone.com [ftp.debug] pure-ftpd: ([EMAIL PROTECTED]) [DEBUG] Command [quit] [] 23:50:20 (2008-11-29) z12.zone.com [ftp.info] pure-ftpd: ([EMAIL PROTECTED]) [INFO] Logout. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on libpq parameters
Owen Hartnett <[EMAIL PROTECTED]> writes: > Yes, it did. I'm confused. My first parameter is a string, but the > following two are integers. I thought the paramType parameter > indicated the type. Do the integers need to be sprintf'd to strings? Yes. Alternatively, you could pass the integers as binary, but that's not notation-free either (you need htonl or some such). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query too slow with "not in" condition
"David Rowley" <[EMAIL PROTECTED]> writes: > I assume workmem, effective_cache_size and random_page_cost are all the same > in the 2 postgresql.conf? Indeed, work_mem is probably the problem. The critical difference between the two plans seems to be that the first one is using a "hashed subplan" and the second one isn't. Assuming the same datatypes in both databases, the only reason not to use a hashed subplan is if the hashtable is estimated not to fit in work_mem. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
On Sun, Nov 30, 2008 at 3:17 AM, Andrus <[EMAIL PROTECTED]> wrote: >> Are you using ftp or lftp? lftp is much more scriptable / controllable >> than regular ftp. > > I'm using ftp as shown in script. > typing lftp shows that this is not found but locate shows something. > How to use lftp in this 3-year old gentoo ? > Where to find sample script for thos task ? Sorry, I'm no gentoo expert. It's certainly not the first distro I'd recommend to someone just starting out with linux / unix. Time to break out the google. I also found quite a few example scripts for lftp with google in a few minutes. here's a simple one. open myftp.ftp.com user username password cd /some_directory/ mv textfile.tar Finishedtextfile.tar get Finishedtextfile.tar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
Are you using ftp or lftp? lftp is much more scriptable / controllable than regular ftp. I'm using ftp as shown in script. typing lftp shows that this is not found but locate shows something. How to use lftp in this 3-year old gentoo ? Where to find sample script for thos task ? Andrus. pg_log # lftp bash: lftp: command not found pg_log # locate lftp /var/cache/edb/dep/usr/portage/net-fs/curlftpfs-0.9 /var/cache/edb/dep/usr/portage/net-ftp/lftp-3.5.6 /var/cache/edb/dep/usr/portage/net-ftp/lftp-3.5.4 /var/cache/edb/dep/usr/portage/net-ftp/glftpd-1.32-r2 /var/cache/edb/dep/usr/portage/net-ftp/glftpd-2.01 /var/cache/edb/dep/usr/portage/net-ftp/glftpd-2.00 /var/cache/edb/dep/usr/portage/net-ftp/lftp-3.4.6 /usr/portage/metadata/cache/net-ftp/glftpd-2.00 /usr/portage/metadata/cache/net-ftp/glftpd-2.01 /usr/portage/metadata/cache/net-ftp/lftp-3.4.6 /usr/portage/metadata/cache/net-ftp/glftpd-1.32-r2 /usr/portage/metadata/cache/net-ftp/lftp-3.5.4 /usr/portage/metadata/cache/net-ftp/lftp-3.5.6 /usr/portage/metadata/cache/net-fs/curlftpfs-0.9 /usr/portage/net-ftp/lftp /usr/portage/net-ftp/lftp/Manifest /usr/portage/net-ftp/lftp/files /usr/portage/net-ftp/lftp/files/lftp-3.4.6-nossl.patch /usr/portage/net-ftp/lftp/files/digest-lftp-3.4.6 /usr/portage/net-ftp/lftp/files/digest-lftp-3.5.4 /usr/portage/net-ftp/lftp/files/digest-lftp-3.5.6 /usr/portage/net-ftp/lftp/lftp-3.4.6.ebuild /usr/portage/net-ftp/lftp/ChangeLog /usr/portage/net-ftp/lftp/metadata.xml /usr/portage/net-ftp/lftp/lftp-3.5.4.ebuild /usr/portage/net-ftp/lftp/lftp-3.5.6.ebuild /usr/portage/net-ftp/glftpd /usr/portage/net-ftp/glftpd/Manifest /usr/portage/net-ftp/glftpd/files /usr/portage/net-ftp/glftpd/files/glftpd-2.00-install.patch /usr/portage/net-ftp/glftpd/files/digest-glftpd-1.32-r2 /usr/portage/net-ftp/glftpd/files/glftpd-2.01-install.patch /usr/portage/net-ftp/glftpd/files/digest-glftpd-2.00 /usr/portage/net-ftp/glftpd/files/digest-glftpd-2.01 /usr/portage/net-ftp/glftpd/files/glftpd.xinetd.d /usr/portage/net-ftp/glftpd/files/glftpd.env.d /usr/portage/net-ftp/glftpd/files/glftpd-2.00-script-path-checks.patch /usr/portage/net-ftp/glftpd/files/1.32-install.patch /usr/portage/net-ftp/glftpd/files/glftpd-2.00-gcc.patch /usr/portage/net-ftp/glftpd/files/1.32-stack-overflow.patch /usr/portage/net-ftp/glftpd/glftpd-2.01.ebuild /usr/portage/net-ftp/glftpd/glftpd-1.32-r2.ebuild /usr/portage/net-ftp/glftpd/glftpd-2.00.ebuild /usr/portage/net-ftp/glftpd/ChangeLog /usr/portage/net-fs/curlftpfs /usr/portage/net-fs/curlftpfs/Manifest /usr/portage/net-fs/curlftpfs/files /usr/portage/net-fs/curlftpfs/files/digest-curlftpfs-0.9 /usr/portage/net-fs/curlftpfs/ChangeLog /usr/portage/net-fs/curlftpfs/metadata.xml /usr/portage/net-fs/curlftpfs/curlftpfs-0.9.ebuild -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query too slow with "not in" condition
> I have loaded the backup from a live database in a test system. Both run > 8.3.5 versions. The plan for a query varies in these systems. > Test System > A. PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 > 20061115 (prerelease) (SUSE Linux) > B. explain select * from stock_transaction_detail_106 where transaction_id > not in (select transaction_id from transaction_value); > Seq Scan on stock_transaction_detail_106 (cost=1829.78..2867.74 rows=16478 width=128) > Filter: (NOT (hashed subplan)) > SubPlan > -> Seq Scan on transaction_value (cost=0.00..1598.02 rows=92702 width=4) > The query takes about 300 ms to run. > Production System > 1. PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux) > 2. explain select * from stock_transaction_detail_106 where transaction_id not in (select transaction_id from transaction_value); > Seq Scan on stock_transaction_detail_106 (cost=2153.95..25245478.39 rows=17064 width=122) > Filter: (NOT (subplan)) > SubPlan > -> Materialize (cost=2153.95..3401.01 rows=92905 width=4) > -> Seq Scan on transaction_value (cost=0.00..1743.05 rows=92905 width=4) > Here the query did not return any results after 1hour. > In both the computers same query with in condition runs fast (520 ms and 290 ms respectively) > Please help me to resolve this issue. (One configuration difference > between these machines are pg_hba.conf file. In production machine it is > password enabled. In test machine it is trust mode.) You might find this page interesting: http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/ I assume workmem, effective_cache_size and random_page_cost are all the same in the 2 postgresql.conf? Do you get the same after you ANALYZE the database? If you read the page above you might realise NOT IN is not really what you want. Maybe NOT EXISTS or a LEFT OUTER JOIN ... WHERE transaction_id IS NULL ? David. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query too slow with "not in" condition
Dear Friends, I have loaded the backup from a live database in a test system. Both run 8.3.5 versions. The plan for a query varies in these systems. Test System A. PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (SUSE Linux) B. explain select * from stock_transaction_detail_106 where transaction_id not in (select transaction_id from transaction_value); Seq Scan on stock_transaction_detail_106 (cost=1829.78..2867.74 rows=16478 width=128) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on transaction_value (cost=0.00..1598.02 rows=92702 width=4) The query takes about 300 ms to run. Production System 1. PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux) 2. explain select * from stock_transaction_detail_106 where transaction_id not in (select transaction_id from transaction_value); Seq Scan on stock_transaction_detail_106 (cost=2153.95..25245478.39 rows=17064 width=122) Filter: (NOT (subplan)) SubPlan -> Materialize (cost=2153.95..3401.01 rows=92905 width=4) -> Seq Scan on transaction_value (cost=0.00..1743.05 rows=92905 width=4) Here the query did not return any results after 1hour. In both the computers same query with in condition runs fast (520 ms and 290 ms respectively) Please help me to resolve this issue. (One configuration difference between these machines are pg_hba.conf file. In production machine it is password enabled. In test machine it is trust mode.) Thanks and regards, Ma Sivakumar மா சிவகுமார் எல்லோரும் எல்லாமும் பெற வேண்டும் http://masivakumar.blogspot.com
