[PostgreSQL] 1 grote query vs. veel kleine queries

[cashewnut]

Ik ben bezig met de ontwikkeling van een webapplicatie. We gebruiken PostgreSQL als database. Nou was ik bezig om een aantal kleine queries die in 1 pagina gebruikt worden, te combineren naar 1 grotere query om performance-winst te halen. Doel van de queries is om het aantal objecten dat een klant heeft terug te geven. Dit is de query:

code:

select
count(distinct us.user_id) as person_count, 
count(distinct c2s.c2s_serv_id) as service_count, 
count(distinct doc.doc_id) as document_count, 
count(distinct pr1.peer_cust_id2) as peering_count_1, 
count(distinct pr2.peer_cust_id1) as peering_count_2, 
count(distinct cur.cur_rack_id) as rack_count, 
count(distinct po.port_id) as port_count, 
count(distinct ip.ipaddr_id) as ipaddress_count, 
count(distinct c2pa.c2pa_patch_id) as patch_count 

from tbl_customer as cu 
left outer join tbl_user2customer as u2c on cu.cust_id = u2c.u2c_cust_id 
left outer join tbl_user as us on (u2c.u2c_user_id = us.user_id AND us.user_auth_id IS NULL) 
left outer join tbl_customer2service as c2s on cu.cust_id = c2s.c2s_cust_id 
left outer join tbl_document as doc on cu.cust_id = doc.doc_cust_id 
left outer join tbl_peering AS pr1 ON cu.cust_id = pr1.peer_cust_id1 
left outer join tbl_peering AS pr2 ON cu.cust_id = pr2.peer_cust_id2 
left outer join tbl_customerrack AS cur ON cu.cust_id = cur.cur_cust_id 
left outer join tbl_port AS po ON cu.cust_id = po.port_cust_id 
left outer join tbl_ipaddress AS ip ON cu.cust_id = ip.ipaddr_cust_id 
left outer join tbl_customer2patch AS c2pa ON cu.cust_id = c2pa.c2pa_cust_id 

where cu.cust_id = 0

Probleem is echter, dat door deze enkele query de pagina zo'n 7-8 keer (!) trager wordt dan met veel losse queries. Voorheen was de pagina in ongeveer 0.09 seconden opgebouwd, nu duurt het bijna 0.8 seconden!

Dus mijn vraag is, doe ik hier iets fout? Of ligt het aan PostgreSQL? De site draait op Solaris 9 (Sparc), PostgreSQL hebben we als sunfreeware package geinstalleerd (postgresql-8.0.1-sol9-sparc-local).

[Janoz]

Ongerelateerde queries proberen samen te voegen uit 'performance' overwegingen is onzin. Bedenk eens hoe groot het tussenresultaat in dit geval gaat worden (bijna cartesisch product van alle tabellen).

De opmerking 'het in 1 query doen'slaat over het algemeen op het 'n+1 querys problem'. In dit geval is daar geen sprake van en zullen de 9 losse queries veel efficienter werken.

[Varienaja]

In pgAdmin zit een hele mooie query-analyzer. Ik zou daarmee gaan spelen als ik jou was. Dan kan je heel mooi zien of je queries optimaal zijn.

[Verwijderd]

Varienaja schreef op donderdag 02 juni 2005 @ 21:20:
In pgAdmin zit een hele mooie query-analyzer. Ik zou daarmee gaan spelen als ik jou was. Dan kan je heel mooi zien of je queries optimaal zijn.

Een query optimiser laat zien waar de knelpunten zitten in de gebruikte query, maar kan geen advies geven over het wel of niet samenvoegen van meerdere queries. Twee optimale queries kunnen beide efficient zijn maar gezamelijk niet de meest optimale query voor het doel.

In dit geval bevat de query heel veel outer joins. Per definitie dus nogal traag. Verschillende losse queries kunnen best wel eens sneller zijn. Zeker aangezien ik het idee heb dat de TS gewoon onafhankelijke data aan het opvragen is. (Het aantal unieke IP nummers, het aantal unieke poorten enz)

[kasper_vk]

En als je dan toch op deze manier 1 tabel wilt relateren aan een hoop anderen en dat in 1 query wilt doen, probeer dan eens een union te gebruiken.

[Varienaja]

Verwijderd schreef op vrijdag 03 juni 2005 @ 08:38:
Een query optimiser laat zien waar de knelpunten zitten in de gebruikte query, maar kan geen advies geven over het wel of niet samenvoegen van meerdere queries. Twee optimale queries kunnen beide efficient zijn maar gezamelijk niet de meest optimale query voor het doel.

Als je eerst de enkele queries analyseert, en daarna de samengevoegde kan je de kosten prima vergelijken.