[postgresql] join optimalizeren?

[MadMan81]

Ik heb de volgende tabellen onder postgresql 7.3:

Create table hosts (
hostid serial primary key,
hostip cidr NOT NULL,
hostname varchar(20)
);

Create table tabel1 (
tbl1ID varchar(20) primary key,
hostid integer references hosts,
amount integer NOT NULL
);

Create index tbl1_index on tabel1 (hostid);

De tabel met hosts telt ongeveer 400 record
En tabel1 telt er 30.000

Als ik nu de volgende query uitvoer:

select hosts.hostname, sum(tabel1.amount) as amount from tabel1 join hosts on hosts.hostid=tabel1.hostid group by hosts.hostname order by amount desc

Dan doet hij er 3,7 sec over @700 Mhz

Op M$ SQL server 7 @1000Mhz doet hij er 0.01 sec over
Hoe kan dat? en wat doe ik fout?

Ik heb de query al wat geoptimalizeerd:

select hosts.hostname, sum(tabel1.amount) as amount from hosts right join (select hostid, sum(amount) as amount from tabel1 group by hostid) as tbl1 on tbl1.hostid=hosts.hostid group by hosts.hostname order by amount desc

Deze doet er nog 0.27 sec over @700Mhz, maar dat is nog veeeel te veel: tabel1 gaat straks ongeveer 1 miljoen records bevatten

Iemand een idee?

[jochemd]

Hoe ziet de EXPLAIN ANALYZE er uit?

[MadMan81]

code:

Sort  (cost=2759.23..2760.01 rows=309 width=40)
   Sort Key: sum(tbl1.amount)
   ->  Aggregate  (cost=2723.28..2746.45 rows=309 width=40)
         ->  Group  (cost=2723.28..2738.73 rows=3090 width=40)
               ->  Sort  (cost=2723.28..2731.00 rows=3090 width=40)
                     Sort Key: hosts.hostname
                     ->  Merge Join  (cost=2435.56..2544.15 rows=3090 width=40)
                           Merge Cond: ("outer".hostid = "inner".hostid)
                           ->  Index Scan using hosts_pkey on hosts  (cost=0.00..52.00 rows=1000 width=28)
                           ->  Sort  (cost=2435.56..2443.29 rows=3090 width=8)
                                 Sort Key: tbl1.hostid
                                 ->  Subquery Scan tbl1  (cost=0.00..2256.43 rows=3090 width=8)
                                       ->  Aggregate  (cost=0.00..2256.43 rows=3090 width=8)
                                             ->  Group  (cost=0.00..2179.18 rows=30902 width=8)
                                                   ->  Index Scan using tbl1_hostid_idx on blocks  (cost=0.00..2101.92 rows=30902 width=8)

deze is dan van de laatste query (die van 0.27 sec)

[ Voor 4% gewijzigd door MadMan81 op 21-05-2003 11:30 ]

[jochemd]

Dat is de EXPLAIN output, niet de EXPLAIN ANALYZE output. En heb je hem van beide queries?

[justmental]

MadMan81 schreef op 20 mei 2003 @ 22:11:
select hosts.hostname, sum(tabel1.amount) as amount from tabel1 join hosts on hosts.hostid=tabel1.hostid group by hosts.hostname order by amount desc

Ik begrijp niet dat die optimizer dat slikt.
Je groepeert op een verld uit de master tabel en sorteert dan op een veld uit de child.
Waarschijnlijk wil je een sortering op sum(tabel1.amount).

[MadMan81]

De snelle query:
select hosts.hostname, sum(tabel1.amount) as amount from hosts right join (select hostid, sum(amount) as amount from tabel1 group by hostid) as tbl1 on tbl1.hostid=hosts.hostid group by hosts.hostname order by amount desc

code:

                                                                                        QUERY PLAN                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=2759.23..2760.01 rows=309 width=40) (actual time=320.11..320.36 rows=409 loops=1)
   Sort Key: sum(tbl1.amount)
   ->  Aggregate  (cost=2723.28..2746.45 rows=309 width=40) (actual time=309.01..316.02 rows=409 loops=1)
         ->  Group  (cost=2723.28..2738.73 rows=3090 width=40) (actual time=308.92..312.52 rows=409 loops=1)
               ->  Sort  (cost=2723.28..2731.00 rows=3090 width=40) (actual time=308.91..309.26 rows=409 loops=1)
                     Sort Key: hosts.hostname
                     ->  Merge Join  (cost=2435.56..2544.15 rows=3090 width=40) (actual time=291.49..297.06 rows=409 loops=1)
                           Merge Cond: ("outer".hostid = "inner".hostid)
                           ->  Index Scan using hosts_pkey on hosts  (cost=0.00..52.00 rows=1000 width=28) (actual time=0.27..2.59 rows=410 loops=1)
                           ->  Sort  (cost=2435.56..2443.29 rows=3090 width=8) (actual time=291.09..291.38 rows=409 loops=1)
                                 Sort Key: tbl1.hostip
                                 ->  Subquery Scan tbl1  (cost=0.00..2256.43 rows=3090 width=8) (actual time=0.55..289.48 rows=409 loops=1)
                                       ->  Aggregate  (cost=0.00..2256.43 rows=3090 width=8) (actual time=0.53..288.36 rows=409 loops=1)
                                             ->  Group  (cost=0.00..2179.18 rows=30902 width=8) (actual time=0.34..248.80 rows=30902 loops=1)
                                                   ->  Index Scan using tabel1_hostid_idx on tabel1  (cost=0.00..2101.92 rows=30902 width=8) (actual time=0.32..161.64 rows=30902 loops=1)
 Total runtime: 322.29 msec
(16 rows)

En de andere, de orginele:
select hosts.hostname, sum(tabel1.amount) as amount from tabel1 join hosts on hosts.hostid=tabel1.hostid group by hosts.hostname order by amount desc

code:

                                                                            QUERY PLAN                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=5801.13..5808.85 rows=3090 width=36) (actual time=3896.91..3897.18 rows=409 loops=1)
   Sort Key: sum(tabel1.amount)
   ->  Aggregate  (cost=5390.23..5621.99 rows=3090 width=36) (actual time=3395.60..3893.95 rows=409 loops=1)
         ->  Group  (cost=5390.23..5544.74 rows=30902 width=36) (actual time=3350.14..3846.48 rows=30902 loops=1)
               ->  Sort  (cost=5390.23..5467.48 rows=30902 width=36) (actual time=3350.11..3411.01 rows=30902 loops=1)
                     Sort Key: hosts.hostname
                     ->  Merge Join  (cost=0.00..2619.95 rows=30902 width=36) (actual time=0.66..594.09 rows=30902 loops=1)
                           Merge Cond: ("outer".hostid = "inner".hostid)
                           ->  Index Scan using tabel1_hostid_idx on blocks  (cost=0.00..2101.92 rows=30902 width=8) (actual time=0.33..198.44 rows=30902 loops=1)
                           ->  Index Scan using hosts_pkey on hosts  (cost=0.00..52.00 rows=1000 width=28) (actual time=0.24..151.52 rows=30903 loops=1)
 Total runtime: 3900.84 msec
(11 rows)

justmental --> Als je de query bekijkt, zie je ook dat ik dat doe (..sum(amount) as amount..). In de host tabel bestaat niet eens een kolom amount

[ACM]

En hoe optimaliseert ie:

code:

SELECT 
  hosts.hostname,
  SUM(tabel1.amount) AS amount
FROM
  hosts, tabel1
WHERE
   hosts.hostid=tabel1.hostid
GROUP BY hosts.hostname
ORDER BY amount DESC

?

Doe sowieso ook eerst eens 'vacuum full analyze' op je tabel en verder geregeld 'vacuum' als je (veel) data hebt toegevoegd.

Probeer ook eens: SET ENABLE_MERGEJOIN=OFF; in psql en kijk of ie dan zonder die "merge join" een stuk vlotter is.

[ Voor 15% gewijzigd door ACM op 22-05-2003 10:25 ]

[MadMan81]

code:

                                                                            QUERY PLAN                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=5801.13..5808.85 rows=3090 width=36) (actual time=3885.33..3885.62 rows=409 loops=1)
   Sort Key: sum(tabel1.amount)
   ->  Aggregate  (cost=5390.23..5621.99 rows=3090 width=36) (actual time=3385.43..3882.48 rows=409 loops=1)
         ->  Group  (cost=5390.23..5544.74 rows=30902 width=36) (actual time=3349.53..3834.66 rows=30902 loops=1)
               ->  Sort  (cost=5390.23..5467.48 rows=30902 width=36) (actual time=3349.51..3400.78 rows=30902 loops=1)
                     Sort Key: hosts.hostname
                     ->  Merge Join  (cost=0.00..2619.95 rows=30902 width=36) (actual time=0.68..586.13 rows=30902 loops=1)
                           Merge Cond: ("outer".hostid = "inner".hostid)
                           ->  Index Scan using tabel1_hostid_idx on tabel (cost=0.00..2101.92 rows=30902 width=8) (actual time=0.37..186.27 rows=30902 loops=1)
                           ->  Index Scan using hosts_pkey on hosts  (cost=0.00..52.00 rows=1000 width=28) (actual time=0.22..160.31 rows=30903 loops=1)
 Total runtime: 3889.26 msec
(11 rows)

De mergejoin uitzetten maakt niet uit: tijd is het zelfde, alleen hij gebruikt nu de hash-join

[ Voor 3% gewijzigd door MadMan81 op 22-05-2003 10:44 ]

[ACM]

Hash join is ook sloom Die zou je dan ook nog uit kunnen zetten

Was dit bovenstaande na een vacuum (full) analyze?

[ Voor 3% gewijzigd door ACM op 22-05-2003 10:45 ]

[MadMan81]

Vacuum analyze lijkt me niet nodig: de tabel is nog in development, en alleen geinsert, nooit geupdate/delete

[jochemd]

code:

->  Index Scan using hosts_pkey on hosts  (cost=0.00..52.00 rows=1000 width=28) (actual time=0.22..160.31 rows=30903 loops=1)

Waarom gebruikt de planner hier de standaard schatting van 1000 rows die er slechts een factor 2.5 vanaf zit? Weet je zeker dat die schatting niet beter wordt als je een VACUUM ANALYZE hebt gedaan?

Wat je volgens mij eens moet doen is het volgende:
CLUSTER hosts_pkey ON hosts;
CLUSTER tabel1_hostid_idx ON tabel;
VACUUM FULL ANALYZE;

En geef dan de EXPLAIN ANLYZE van die query van ACM eens.

[MadMan81]

code:

                                                               QUERY PLAN                           
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4430.93..4438.66 rows=3090 width=30) (actual time=3600.16..3600.44 rows=409 loops=1)
   Sort Key: sum(tabel1.amount)
   ->  Aggregate  (cost=4020.04..4251.80 rows=3090 width=30) (actual time=3095.64..3598.02 rows=409 loops=1)
         ->  Group  (cost=4020.04..4174.55 rows=30902 width=30) (actual time=3059.62..3551.72 rows=30902 loops=1)
               ->  Sort  (cost=4020.04..4097.29 rows=30902 width=30) (actual time=3059.59..3120.03 rows=30902 loops=1)
                     Sort Key: hosts.hostname
                     ->  Hash Join  (cost=9.12..1281.93 rows=30902 width=30) (actual time=2.59..293.40 rows=30902 loops=1)
                           Hash Cond: ("outer".hostid = "inner".hostid)
                           ->  Seq Scan on tabel1  (cost=0.00..732.02 rows=30902 width=8) (actual time=0.11..127.59 rows=30902 loops=1)
                           ->  Hash  (cost=8.10..8.10 rows=410 width=22) (actual time=2.35..2.35 rows=0 loops=1)
                                 ->  Seq Scan on hosts  (cost=0.00..8.10 rows=410 width=22) (actual time=0.09..1.38 rows=410 loops=1)
 Total runtime: 3603.41 msec
(12 rows)

Nu doet hij ineens een hash-join, maar ik heb die var "ENABLE_MERGEJOIN" weer op ON gezet, maar dat maakt niet uit..

Bij Seg Scan on hosts staat nu wel 410 rows

[xoror]

MadMan81 schreef op 22 May 2003 @ 10:54:
Vacuum analyze lijkt me niet nodig: de tabel is nog in development, en alleen geinsert, nooit geupdate/delete

eh...... vacuum analyze update de statistics voor de optimizer. als je records insert worden statistic voor de betreffende table anders. immers er komen meer records bij. het heeft dus wel degelijk nut, ook al is het voor development (en zeker vooral als er veel records worden geinsert).


Maar waar je nu mee zit is een algemeen probleem. aggregatie funkties zijn gewoon traag bij grote dataset. Ik vermoed dat mssl het wat slimmer aanpakt. je zou bijv zelf een table bij kunnen houden met de sum, max etc, bij update laat je een trigger afgaan die het update. Als je sum, max oid nodig heb select je gewoon uit die table.

[ACM]

xoror schreef op 22 May 2003 @ 14:25:
Maar waar je nu mee zit is een algemeen probleem. aggregatie funkties zijn gewoon traag bij grote dataset.

Neemt niet weg dat dit een erg slome aggregatie is voor 30k records.

Btw probeer ook het volgende nog eens:

code:

SELECT 
  tabel1.hostid
  SUM(tabel1.amount) AS amount
FROM
  tabel1
GROUP BY tabel1.hostid

Dan weet je zeker of het een slome aggregate is of dat het ergens aan de join ligt.

[MadMan81]

Ok, dan is hier de analyze van de boven genoemde query:

code:

                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..987.43 rows=3090 width=8) (actual time=0.52..283.90 rows=409 loops=1)
   ->  Group  (cost=0.00..910.18 rows=30902 width=8) (actual time=0.35..246.00 rows=30902 loops=1)
         ->  Index Scan using tabel1_hostid_idx on tabel1  (cost=0.00..832.92 rows=30902 width=8) (actual time=0.32..160.77 rows=30902 loops=1)
 Total runtime: 284.52 msec
(4 rows)

Heb nog ff wat testjes gedaan: het komt door de group by (samen met de sum(amount)) dan groeit die tijd echt hard.

Op zich wel logisch: je group by gaat op een varchar, en niet op een integer. Maar hij wil het niet slikken om hostname weer te geven en op hostid te groeperen.

Hoe moet ik trouwens die getallen (bij cost=.. of actual time=..) zien?

[drm]

offtopic:
Lees hiervan even puntje 4.

[jochemd]

MadMan81 schreef op 22 May 2003 @ 17:17:
Heb nog ff wat testjes gedaan: het komt door de group by (samen met de sum(amount)) dan groeit die tijd echt hard.

Op zich wel logisch: je group by gaat op een varchar, en niet op een integer. Maar hij wil het niet slikken om hostname weer te geven en op hostid te groeperen.

Werkt GROUP BY hostid, hostname niet?
Het lijkt me typisch een gevalletje voor hashaggregates, maar 7.4 duurt nog wel een paar maanden.

Hoe moet ik trouwens die getallen (bij cost=.. of actual time=..) zien?

Het eerste getal achter time is wanneer de eerste tuple terugkomt, het tweede getal wanneer de laatste terugkomt. De rest spreekt denk ik voor zich.

[MadMan81]

GROUP BY hostid, hostname werkt wel, maar dat scheelt niet in tijd of zo, das wel jammer..

[jochemd]

Probeer de volgende eens. En als hij een mergejoin doet, wat gebeurt er als je hem dwingt tot een hashjoin?

code:

SELECT
    h.hostname,
    t.amount
FROM
    hosts h,
    (
        SELECT
            hostid,
            SUM(amount) AS amount
        FROM
            tabel1
        GROUP BY
            hostid
    ) t
WHERE
    t.hostid = h.hostid
ORDER BY
    t.amount DESC

[ Voor 4% gewijzigd door jochemd op 22-05-2003 22:18 ]

[MadMan81]

hoe doet hem met een hash join in 0.257 sec. Merge join doet hij niet.. (tenzij ik zeg enable hashjoin off') Maar in tijd maat dat niet uit..

Nu ik er goed naar kijk is het eigenlijk een verbeterde versie van mijn query. De ananlyze is wel een stuk korter!

[MadMan81]

Niemand een idee hoe je deze verder kunt optimalizeren?

Of ligt het misschien aan de database instellingen (zijn die niet helemaal optimaal?). Zo ja, bij welke instellingen moet ik zijn dan?

[xoror]

Je query doet er nu al 0,3 sec over. moet je nagaan als je dataset straks miljoenen records bevat. Dan is de aggregatie de bottleneck. (insert maar een miljoen records in je test db, dat is toch ook het werkelijk aantal records wat je gaat verwachten bij je systeem? Dan moet je het wel met die grootte testen)

Los het op zoals ik het in mijn vorige posting heb uitgelegd, je query wordt net zo snel als mssql je select immers maar direct 1 value. Het inserten, updaten en deleten gaat heel iets trager, maar das niet z'n ramp. Ik vermoed dat mssql ook intern z'n truuk doet.

Als je data set groot is, kan je de aggregatie weglaten uit je query, dan kan je zien of het de join is die zoveel tijd in beslag neemt. De join zal vermoedelijk wel vlot gaan (indien je indices goed staan en memory settings ok zijn). Anders is het beetje verspilde moeite om de join proberen te optimaliseren terwijl het probleem wellicht ergens anders zit.

[MadMan81]

Ok, ik dat eens gaan proberen.

Maar nog een paar dingentjes:

- Wanneer liggen mijn indices goed? Ik heb een index op de PK's en op hostid in tabel1.
- Hoe moeten je mem settings staan? ze staan nu default (als het beschreven staat in /var/lib/pgsql/data/postgresql.conf)

[xoror]

http://www.postgresql.org...ile=kernel-resources.html
en
http://www.phpbuilder.com...0010821.php3?print_mode=1
om mee te beginnen.

de shared buffer settings is belangrijk. Als je veel grote data sorteert is de sortmem ook belangrijk. (je voorkomt dat ie disk space gaat usen bij sorteren)

voor tables met idices die hard groeien of vaak veranderen, moet je ook een keer in de zoveel tijd reindex (in mijn geval bijv 1x per maand, omdat ik ook veel turnover van data heb, het scheelt dan per maand z'n 100MB) commando gebruiken.

voor de geinteresseerden pgsql 7.3.3 is al sinds een paar dagen te downloaden van http://www.ca.postgresql.org/ftpsite/pub/source/v7.3.3/ hele waslijst aan bugs gesquashed.

[ Voor 43% gewijzigd door xoror op 25-05-2003 15:34 ]

[tanzu]

Ik had ongeveer hetzelfde probleem.

Ik werkte aan iemands code, ik zag dat hij 2 queries had, waar ik dacht dat wel ff beter te doen in 1 query met een join, met een count. Vooral omdat ik moest sorteren.

Met offset 0 limit 1 deed hij er al 261,136.942 ms over, (terwijl ik een klantid had gespecifiseerd die maar weinig items onder zich had, dus hij hoefde maar 20 dingetjes te tellen). Ik heb nooit echt gewacht tot hij klaar was met zon 90.000 items, verdeeld over 100 klanten ofzo.

Hetzelfde bereiken in 2 queries is vele malen sneller, daarnaast denk ik dat de oplossing vwb het aanmaken van een extra tabel die de hoeveelheden bijhoudt misschien nog het allerbeste is, vooral omdat ik ook wil laten sorteren op hoeveelheid enzo.

[ACM]

Je voorbeeld bevat zo weinig detail-informatie dat het absoluut niet verstandig is dat als algemene richtlijn te hanteren. In sommige gevallen is het inderdaad beter losse queries te gebruiken in PostgreSQL, maar in andere gevallen juist weer beter om de queries te joinen of met subqueries samen te laten werken.
Jouw 261 seconden durende query doet me eerder denken dat je op een bepaalde plek een index mist, geen analyze uitgevoerd hebt of uberhaupt niet helemaal de beste join/gecombineerde query had. Maar ik moet er dan wel direct achteraan zeggen dat ik ook al wel een paar keer meegemaakt heb dat het combineren van queries slomer was dan het los uitvoeren.

[ Voor 3% gewijzigd door ACM op 18-06-2005 11:34 ]