Re: [SQL] Joining bug????

>
>
>
> 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