Dear Torsten and friends,
This is another good case to analyse why the query performance is not the same :(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
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 subqueryThe analyse result :
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
"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.foertsch@xxxxxxx> wrote:
On 01/12/13 13:40, Hengky Liwandouw wrote:Without the "explain (analyze,buffers) ..." it's hard to say. A CTE is
> 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 ?
>
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 descwould be interesting to see the "explain (analyze,buffers)" output for
limit 1000
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
