Re: [SQL] Joining bug????
От | David Hartwig |
---|---|
Тема | Re: [SQL] Joining bug???? |
Дата | |
Msg-id | 363613B5.B42316CC@insightdist.com обсуждение исходный текст |
Ответ на | Re: [SQL] Joining bug???? (pierre@desertmoon.com) |
Список | pgsql-sql |
I would try the following indices: prod - (prod_id) pos - (pos_os_id, pos_prod_id) pcat: - (pcat_prod_id, pcat_cat_id) pkey - ( pkey_keyword, pkey_prod_id) Various permutations of order may also effect the outcome. pierre@desertmoon.com wrote: > > > > > > > > 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
В списке pgsql-sql по дате отправления: