about new join syntax performance
От | Gyozo Papp |
---|---|
Тема | about new join syntax performance |
Дата | |
Msg-id | 00a201c0daf6$6e0b05c0$524ac5d5@jaguar обсуждение исходный текст |
Ответы |
Re: about new join syntax performance
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: