Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.1/8.9.1) with ESMTP id SAA03498 for ; Tue, 27 Oct 1998 18:19:33 -0500 (EST) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id RAA16573; Tue, 27 Oct 1998 17:19:27 -0600 (CST) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (4.1) id xmac16537; Tue, 27 Oct 98 17:19:25 -0600 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id <4KHRKFKT>; Tue, 27 Oct 1998 17:21:41 -0600 Message-ID: From: "Jackson, DeJuan" To: Howie , "Gene Selkov Jr." Cc: pierre , pgsql-sql@postgreSQL.org Subject: RE: [SQL] Joining bug???? Date: Tue, 27 Oct 1998 17:21:40 -0600 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain > On Mon, 26 Oct 1998, Gene Selkov Jr. wrote: > > > > > > I've been attempting to get my DB tuned to handle joins as best as > > > > possible. However, no matter which way I try to perform the joins, > if I > > > attempt to join more than two or three tables the joins becomes > > > unbearably long not matter how many indexes I create in however > many ways. > > > > Ditto. Never saw a quadruple join succeed, even with relatively > small tables. > > Ive been playing with the idea of moving a client from MySQL to > Postgres ( > views, triggers, and subselects would _really_ make my life easier ). > > ---[ CUT ]--- > SELECT > account.accnum, > account.accname, > ((customers.firstname || ' ') || customers.lastname) as > Customer, > acctype.descr, > account.balance, > account.status, > billdate.next > FROM > account,customers,acctype,billdate > WHERE > account.custnum=customers.custnum AND > account.acctype=acctype.accid AND > account.accnum=billdate.accnum AND > account.accnum<20 > ORDER BY > account.accnum > ---[ CUT ]--- > > works perfectly, takes about 2 seconds to complete. granted i have > ( sometimes unique ) indexes on all the join columns. > > account has 7k rows, customers has 5k rows, acctype has 12 rows, > billdate has 7k rows. > > a somewhat modified, real-world query ends up crashing postgres, > though: > > ---[ CUT ]--- > select > account.accnum, > account.accname, > account.totalfee, > billdate.next, > ((customers.firstname || ' ') || customers.lastname) as > Customer, > customers.company, > customers.street, > acctype.yearly > FROM > account,customers,acctype,billdate > where > account.totalfee>0.00 AND > billtype=1 OR (billcc1stmo=1 AND > account.created=billdate.last) > AND try: (billdate.billtype=1 OR (billdate.billcc1stmo=1 AND account.created=billdate.last)) AND > balance<>0.00 AND > billdate.next>='1998-01-01' AND > billdate.next<='1998-01-05' AND > account.status<>'C' AND > billdate.accnum=account.accnum AND > account.custnum=customers.custnum AND > account.acctype=acctype.accid > ---[ CUT ]--- > > > > > My only solution was to create a hybrid table that contains the > join of > > > all of the tables I'm searching on with multi-keyed indexes. This > is a > > > VERY kludgy solution that makes changing the keys to my DB hard to > change. > > > > The solution I use may be the worst imaginable kludge, but it works > > great: I do the joins (as well as other set operations) on the > client > > side. Perl hashes are very good for that. > > try making a view. from what others have said, views save the query > plan > and have usually, at least for me, been _alot_ faster than normal > queries. > > --- > Howie URL: http://www.toodarkpark.org > "Oh my god, they killed init! YOU BASTARDS!" >