Received: from desertmoon.com (qmailr@desertmoon.com [198.182.124.2]) by hub.org (8.9.1/8.9.1) with SMTP id LAA11188 for ; Tue, 27 Oct 1998 11:55:04 -0500 (EST) (envelope-from pierre@desertmoon.com) From: pierre@desertmoon.com Received: (qmail 16813 invoked by uid 100); 27 Oct 1998 17:00:02 -0000 Message-ID: <19981027170002.16812.qmail@desertmoon.com> Date: 27 Oct 1998 10:00:02 -0700 Subject: Re: [SQL] Joining bug???? To: daveh@insightdist.com (David Hartwig) Date: Tue, 27 Oct 1998 10:00:01 -0700 (MST) Cc: pgsql-sql@postgreSQL.org In-Reply-To: <3635F3DA.F84CA6DB@insightdist.com> from "David Hartwig" at Oct 27, 98 11:24:58 am X-Mailer: ELM [version 2.4 PL25] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > > > > pierre 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. > > > > 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. > > > > IS there a problem with postgresql in performing joins? Is there a fix? > > What gives? > > There are many reasons for a query to take more time than expected. > PostgreSQL can do reasonably well joining many tables. It is impossible for > anyone to even guess without more specifics. > > Try submitting the specific query and the result of an EXPLAIN. > Example: > EXPLAIN SELECT foo FROM bar; > > Table sizes, indices, and time elapsed are also helpful. This query takes about 30seconds...way too long for my needs...I've got the query down to 2-5 seconds by using a hybrid table that is the join between the pos and pcat tables. All _id fields are indexed, as is the keyword field. explain select p.prod_name from prod p, pos o, pcat c, pkey k where p.prod_id = o.pos_prod_id and o.pos_os_id = 2 and o.pos_prod_id = c.pcat_prod_id and c.pcat_cat_id = 6 and c.pcat_prod_id = k.pkey_prod_id and k.pkey_keyword = 'photoshop'; NOTICE: QUERY PLAN: Nested Loop (cost=8.10 size=2 width=28) -> Nested Loop (cost=6.05 size=1 width=12) -> Nested Loop (cost=4.05 size=1 width=8) -> Index Scan on o (cost=2.05 size=1 width=4) -> Index Scan on c (cost=2.00 size=2 width=4) -> Index Scan on k (cost=2.00 size=2 width=4) -> Index Scan on p (cost=2.05 size=85442 width=16) select count(*) from prod; EXPLAIN count ----- 85443 (1 row) select count(*) from pos; count ------ 132564 (1 row) select count(*) from pcat; count ------ 337251 (1 row) select count(*) from pkey; count ------ 492550 (1 row) EOF > > BTW, in case you don't know, the "VACUUM ANALYZE" statement need to be run > occasionally to facilitate the query planner. > All data is newley imported and indexed. So I shouldn't have to perform this. This web site that is using this DB is still in debug mode. Thanks for taking a look. -=pierre