Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 89C6D9F95C9 for ; Mon, 13 Aug 2007 17:15:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 42343-02 for ; Mon, 13 Aug 2007 17:15:11 -0300 (ADT) X-Greylist: delayed 00:32:35.425446 by SQLgrey-1.7.5 Received: from adficio.mailchannels.com (adficio.mailchannels.com [216.187.77.228]) by postgresql.org (Postfix) with ESMTP id AAE629F9C4C for ; Mon, 13 Aug 2007 17:15:13 -0300 (ADT) Received: by adficio.mailchannels.com (Postfix, from userid 1002) id 25BF7180809; Mon, 13 Aug 2007 12:38:18 -0700 (PDT) Date: Mon, 13 Aug 2007 12:42:34 -0700 From: Ken Simpson To: pgsql-sql@postgresql.org Subject: Re: Comparing two slices within one table efficiently Message-ID: <20070813194234.GC30009@pico> Reply-To: Ken Simpson References: <1694202581-1187027999-cardhu_decombobulator_blackberry.rim.net-401169594-@bxe119.bisx.prod.on.blackberry> <46C0B01C.3020307@sprocks.gotdns.com> <20070813193427.76840@gmx.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; x-action=pgp-signed Content-Disposition: inline In-Reply-To: <20070813193427.76840@gmx.net> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/115 X-Sequence-Number: 29053 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Christian Kindler [13/08/07 21:34 +0200]: > Yes and you could make it even more speedy with the use table partitioning. > http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html Thanks for all your speedy help, everyone. I tried doing a "self join" and that sped things up enormously (query took on the order of 30 seconds to compare two million-row table slices, resulting in a 20K row result). I will also try re-ordering the unique constraint to get speedier indexing out of it and will look at table partitioning. Regards, Ken - -- Ken Simpson CEO, MailChannels Fax: +1 604 677 6320 Web: http://mailchannels.com MailChannels - Reliable Email Delivery (tm) -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGwLQq2YHPr/ypq5QRApP8AKDfRGqDFkcONh0YaojX7362nXP12gCg3WZ6 k5ZBwcMplXyVkEguQtbgdFU= =bsyu -----END PGP SIGNATURE-----