From: | "Bruno BAGUETTE" <pgsql-ml(at)baguette(dot)net> |
---|---|
To: | "'Josh Berkus'" <josh(at)agliodbs(dot)com>, "'Bruno BAGUETTE'" <pgsql-ml(at)baguette(dot)net>, <pgsql-performance(at)postgresql(dot)org> |
Subject: | RE : Large querie with several EXISTS which will be often runned |
Date: | 2003-06-28 09:17:42 |
Message-ID: | !~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAATBuXKOMvlkWzD3KJN6FWLMKAAAAQAAAAMZDMAFkAG0K6t6raV9fLGwEAAAAA@baguette.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello Josh,
> > I will have to manage more or less 10.000 products with
> more or less
> > 2-3 options by products and more or less 40 options-groups.
> >
> > Do you think that this query will be hard for PostgreSQL (currently
> > 7.2.1 but I will migrate to 7.3.2 when going in production
> > environment) ? How can I improve that query to be faster ?
>
> Collapse the inner EXISTS into a straight join in the outer
> EXISTS. Since you
> are merely checking for existence, there is no reason for the
> subquery
> nesting.
Do you mean this query ?
SELECT
products_options_groups.pk_prdoptgrp_id,products_options_groups.prdoptgr
p_name
FROM products_options_groups
WHERE EXISTS
(
SELECT *
FROM products_options_classification
INNER JOIN products_options ON products_options.pk_prdopt_id =
products_options_classification.fk_prdopt_id
WHERE products_options_classification =
products_options_groups.pk_prdoptgrp_id
AND products_options.fk_prd_id = [A PRODUCT ID WRITTEN HERE BY
MY APP]
)
ORDER BY products_options_groups.prdoptgrp_name;
An other question, do you think that my tables are OK or is there some
things I could change in order to have as much performance as possible
(without de-normalize it because I want to avoid redundancy in my
tables).
Thanks very much for your tips ! :-)
---------------------------------------
Bruno BAGUETTE - pgsql-ml(at)baguette(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Shridhar Daithankar | 2003-06-28 09:35:00 | Re: Large querie with several EXISTS which will be often runned |
Previous Message | Arjen van der Meijden | 2003-06-27 20:55:40 | Re: Memory question |