sorry, for now, work_mem=100MB
On Mon, Dec 2, 2013 at 12:33 AM, Hengky Lie <hengkyliwand...@gmail.com>wrote: > Dear Torsten and friends, > > This is another good case to analyse why the query performance is not the > same : > > There are 2 query : > (1) > > with qry1 as ( > select subkategori, kodebarang as produkid, namabarang, keluar, > tbltransaksi.modal*keluar as ttlmodal, > case > when tbltransaksi.discount<=100 then > keluar*(harga - (discount/100*harga)) > > when tbltransaksi.discount>100 > then keluar*(harga-discount) > end as jumlah > from tblpenjualan > join tbltransaksi on tblpenjualan.id=tbltransaksi.jualid > join tblproduk on tbltransaksi.kodebarang=tblproduk.produkid > join tblsubkategori on > tblproduk.subkategoriid=tblsubkategori.tblsubkategoriid > join tblkategori on > tblkategori.kategoriid=tblsubkategori.kategoriid > where tblpenjualan.tanggal between '01/01/13' and '31/10/13') > > > select subkategori,produkid, namabarang , sum(keluar) as ttlkeluar, > sum(jumlah) as jumlah, sum(ttlmodal) as ttlmodal > from qry1 > group by subkategori, produkid, namabarang > > "QUERY PLAN" > "HashAggregate (cost=99124.61..99780.94 rows=65633 width=334) (actual > time=3422.786..3434.511 rows=24198 loops=1)" > " Buffers: shared hit=14543" > " CTE qry1" > " -> Hash Join (cost=11676.07..76153.06 rows=656330 width=73) (actual > time=181.683..2028.046 rows=657785 loops=1)" > " Hash Cond: ((tbltransaksi.kodebarang)::text = > (tblproduk.produkid)::text)" > " Buffers: shared hit=14543" > " -> Hash Join (cost=7247.75..44651.13 rows=656330 width=31) > (actual time=84.885..787.029 rows=658438 loops=1)" > " Hash Cond: (tbltransaksi.jualid = tblpenjualan.id)" > " Buffers: shared hit=13204" > " -> Seq Scan on tbltransaksi (cost=0.00..18730.83 > rows=807283 width=35) (actual time=0.005..157.004 rows=807033 loops=1)" > " Buffers: shared hit=10658" > " -> Hash (cost=5293.64..5293.64 rows=156329 width=4) > (actual time=84.842..84.842 rows=154900 loops=1)" > " Buckets: 16384 Batches: 1 Memory Usage: 3631kB" > " Buffers: shared hit=2546" > " -> Seq Scan on tblpenjualan (cost=0.00..5293.64 > rows=156329 width=4) (actual time=0.007..49.444 rows=154900 loops=1)" > " Filter: ((tanggal >= '2013-01-01'::date) AND > (tanggal <= '2013-10-31'::date))" > " Rows Removed by Filter: 27928" > " Buffers: shared hit=2546" > " -> Hash (cost=3364.19..3364.19 rows=85130 width=55) (actual > time=96.736..96.736 rows=84701 loops=1)" > " Buckets: 16384 Batches: 1 Memory Usage: 6323kB" > " Buffers: shared hit=1339" > " -> Hash Join (cost=5.35..3364.19 rows=85130 width=55) > (actual time=0.241..62.038 rows=84701 loops=1)" > " Hash Cond: ((tblproduk.subkategoriid)::text = > (tblsubkategori.tblsubkategoriid)::text)" > " Buffers: shared hit=1339" > " -> Seq Scan on tblproduk (cost=0.00..2188.30 > rows=85130 width=45) (actual time=0.008..17.549 rows=85035 loops=1)" > " Buffers: shared hit=1337" > " -> Hash (cost=4.23..4.23 rows=90 width=17) (actual > time=0.224..0.224 rows=90 loops=1)" > " Buckets: 1024 Batches: 1 Memory Usage: 4kB" > " Buffers: shared hit=2" > " -> Hash Join (cost=1.09..4.23 rows=90 > width=17) (actual time=0.028..0.153 rows=90 loops=1)" > " Hash Cond: > ((tblsubkategori.kategoriid)::text = (tblkategori.kategoriid)::text)" > " Buffers: shared hit=2" > " -> Seq Scan on tblsubkategori > (cost=0.00..1.90 rows=90 width=21) (actual time=0.005..0.029 rows=90 loops=1)" > " Buffers: shared hit=1" > " -> Hash (cost=1.04..1.04 rows=4 width=4) > (actual time=0.011..0.011 rows=4 loops=1)" > " Buckets: 1024 Batches: 1 Memory > Usage: 1kB" > " Buffers: shared hit=1" > " -> Seq Scan on tblkategori > (cost=0.00..1.04 rows=4 width=4) (actual time=0.002..0.004 rows=4 loops=1)" > " Buffers: shared hit=1" > " -> CTE Scan on qry1 (cost=0.00..13126.60 rows=656330 width=334) (actual > time=181.687..2556.526 rows=657785 loops=1)" > " Buffers: shared hit=14543" > "Total runtime: 3454.442 ms" > > (2)this is exactly the same query with no.1 except it uses subquery > > select subkategori,produkid, namabarang , sum(keluar) as ttlkeluar, > sum(jumlah) as jumlah, sum(ttlmodal) as ttlmodal from > > ( select subkategori, kodebarang as produkid, namabarang, keluar, > tbltransaksi.modal*keluar as ttlmodal, > case > when tbltransaksi.discount<=100 then > keluar*(harga - (discount/100*harga)) > > when tbltransaksi.discount>100 > > then keluar*(harga-discount) > end as jumlah > from tblpenjualan > join tbltransaksi on tblpenjualan.id=tbltransaksi.jualid > join tblproduk on tbltransaksi.kodebarang=tblproduk.produkid > > join tblsubkategori on > tblproduk.subkategoriid=tblsubkategori.tblsubkategoriid > join tblkategori on > tblkategori.kategoriid=tblsubkategori.kategoriid > where tblpenjualan.tanggal between '01/01/13' and '31/10/13') > > as dt group by subkategori, produkid, namabarang > > The analyse result : > > "QUERY PLAN" > "GroupAggregate (cost=124800.44..157616.94 rows=656330 width=73) (actual > time=13895.782..15236.212 rows=24198 loops=1)" > " Buffers: shared hit=14543" > " -> Sort (cost=124800.44..126441.26 rows=656330 width=73) (actual > time=13895.750..14024.911 rows=657785 loops=1)" > " Sort Key: tblsubkategori.subkategori, tbltransaksi.kodebarang, > tblproduk.namabarang" > " Sort Method: quicksort Memory: 103431kB" > " Buffers: shared hit=14543" > " -> Hash Join (cost=11676.07..61385.63 rows=656330 width=73) > (actual time=177.521..1264.431 rows=657785 loops=1)" > " Hash Cond: ((tbltransaksi.kodebarang)::text = > (tblproduk.produkid)::text)" > " Buffers: shared hit=14543" > " -> Hash Join (cost=7247.75..44651.13 rows=656330 width=31) > (actual time=84.473..739.064 rows=658438 loops=1)" > " Hash Cond: (tbltransaksi.jualid = tblpenjualan.id)" > " Buffers: shared hit=13204" > " -> Seq Scan on tbltransaksi (cost=0.00..18730.83 > rows=807283 width=35) (actual time=0.005..146.601 rows=807033 loops=1)" > " Buffers: shared hit=10658" > " -> Hash (cost=5293.64..5293.64 rows=156329 width=4) > (actual time=84.429..84.429 rows=154900 loops=1)" > " Buckets: 16384 Batches: 1 Memory Usage: 3631kB" > " Buffers: shared hit=2546" > " -> Seq Scan on tblpenjualan (cost=0.00..5293.64 > rows=156329 width=4) (actual time=0.008..48.968 rows=154900 loops=1)" > " Filter: ((tanggal >= '2013-01-01'::date) AND > (tanggal <= '2013-10-31'::date))" > " Rows Removed by Filter: 27928" > " Buffers: shared hit=2546" > " -> Hash (cost=3364.19..3364.19 rows=85130 width=55) (actual > time=92.998..92.998 rows=84701 loops=1)" > " Buckets: 16384 Batches: 1 Memory Usage: 6323kB" > " Buffers: shared hit=1339" > " -> Hash Join (cost=5.35..3364.19 rows=85130 width=55) > (actual time=0.240..59.587 rows=84701 loops=1)" > " Hash Cond: ((tblproduk.subkategoriid)::text = > (tblsubkategori.tblsubkategoriid)::text)" > " Buffers: shared hit=1339" > " -> Seq Scan on tblproduk (cost=0.00..2188.30 > rows=85130 width=45) (actual time=0.008..16.942 rows=85035 loops=1)" > " Buffers: shared hit=1337" > " -> Hash (cost=4.23..4.23 rows=90 width=17) > (actual time=0.221..0.221 rows=90 loops=1)" > " Buckets: 1024 Batches: 1 Memory Usage: 4kB" > " Buffers: shared hit=2" > " -> Hash Join (cost=1.09..4.23 rows=90 > width=17) (actual time=0.028..0.142 rows=90 loops=1)" > " Hash Cond: > ((tblsubkategori.kategoriid)::text = (tblkategori.kategoriid)::text)" > " Buffers: shared hit=2" > " -> Seq Scan on tblsubkategori > (cost=0.00..1.90 rows=90 width=21) (actual time=0.006..0.046 rows=90 loops=1)" > " Buffers: shared hit=1" > " -> Hash (cost=1.04..1.04 rows=4 > width=4) (actual time=0.012..0.012 rows=4 loops=1)" > " Buckets: 1024 Batches: 1 > Memory Usage: 1kB" > " Buffers: shared hit=1" > " -> Seq Scan on tblkategori > (cost=0.00..1.04 rows=4 width=4) (actual time=0.002..0.005 rows=4 loops=1)" > " Buffers: shared hit=1" > "Total runtime: 15244.038 ms" > > This is my Postgresqlconf : > max_connections=50 > shared_buffers=1024MB > wall_buffers=16MB > max_prepared_transactions=0 > work_mem=50MB > maintenance_work_mem=256MB > > Thanks > > > On Sun, Dec 1, 2013 at 9:39 PM, Torsten Förtsch > <torsten.foert...@gmx.net>wrote: > >> On 01/12/13 13:40, Hengky Liwandouw wrote: >> > Torsten, your 2nd option works now. I dont know maybe copy and paste >> error. I just want to report that your 2nd option with work_mem=100MB >> required the same amount of time (about 58 seconds), while my query >> required 4.9 seconds. >> > >> > What make this two query so different ? >> > >> Without the "explain (analyze,buffers) ..." it's hard to say. A CTE is >> currently a way to trick the query planner because it's planned >> separately. A subquery on the other hand is integrated in the outer >> query and planned/optimized as one thing. >> >> If your planner parameters are correctly set up, the subquery should >> almost always outrun the CTE. Often, though, not much. >> >> Now, you may ask why CTE then exist at all? There are things that cannot >> be expressed without them, in particular WITH RECURSIVE. >> >> The fact that it performs so badly as a subquery indicates that either >> your table statistics are suboptimal or more probably the planner >> parameters or work_mem. >> >> Another point I have just noticed, how does it perform if you change >> >> and extract(... from tanggal)='2013' >> >> to >> >> and '2013-01-01'::date <= tanggal >> and tanggal < '2013-01-01'::date + '1 year'::interval >> >> Also, I think it would be possible to even get rid of the subquery. At >> least you can get rid of the tanggal and jumlah output from the subquery. >> >> select s.id, s.nama, t.kodebarang, p.namabarang, >> sum(case when extract(month from t.tanggal) = 1 >> then t.keluar else 0 end) as jan, >> sum(case when extract(month from t.tanggal) = 2 >> then t.keluar else 0 end) as feb, >> ..., >> sum(t.keluar) as total >> from tbltransaksi t >> join tblproduk p on t.kodebarang=p.produkid >> join tblsupplier s on p.supplierid=s.id >> where (t.jualid is not null or t.returjualid is not null) >> and '2013-01-01'::date <= t.tanggal >> and t.tanggal < '2013-01-01'::date + '1 year'::interval >> group by s.id, s.nama, t.kodebarang, p.namabarang >> order by total desc >> limit 1000 >> >> would be interesting to see the "explain (analyze,buffers)" output for >> the query above. >> >> Please double-check the query. I think it should do exactly the same as >> your query. But you know, shit happens. >> >> BTW, am I right in assuming that you are from Malaysia or Indonesia? I >> am trying to learn a bit of Malay. I am a complete beginner, though. >> >> Selamat berjaya (is that possible to wish you success?) >> Torsten >> > >
