Hello,
we are working with v7.0.2 but to upgrade to the recent version of postgres regarding to the full join syntax most
demandedby our web-application.
I'd like to get some tips about how to rewrite our queries to achieve better performance.
[ from the manual 2.1.1.2. Subqueries in FROM clause ]
" More interesting cases, which can't be reduced to a plain join, arise when the subquery involves grouping or
aggregation."
Take a look at this query:
SELECT * FROM (SELECT did, count(*) FROM values WHERE ... GROUP BY did HAVING count(*) > ...) AS V(did, ok) JOIN
documentAS D USING(did);
My question is this significantly faster than two separate queries which are used now (in v7.0.2) via PHP (libpq):
SELECT did, count(*) INTO TEMPORARY TABLE tmp FROM values WHERE ... GROUP BY did HAVING count(*) > ...;
SELECT * FROM tmp AS V, document AS D WHER V.did = D.did;
additional informations:
table document:
did serial primary key,
content text not null
total rows = 22.000 -
table values :
did serial references (document),
value text not null,
/* other columns */
total rows = 2 or 5 times of document rows, (there are 2 up to 5 references to each document)
I'd like to know whether it's worth changing the scripts to use the new join syntax.
Papp Gyozo
- pgerzson@freestart.hu
