Hello
I tested speed SELF JOIN and correlated subquery for couting of subtotals:
It's strange, so correlated subqueries is faster, but it has much higher cost:
postgres=# explain analyze select t1.id, t1.sale_date, t1.product,
t1.sale_price, sum(t2.sale_price) from
history t1 inner join history t2 on t1.id >= t2.id and t1.product = t2.product
group by t1.id, t1.sale_date, t1.product, t1.sale_price
order by t1.id
;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=3678.85..3691.36 rows=5003 width=19) (actual
time=1553.575..1560.618 rows=5003 loops=1)
Sort Key: t1.id
Sort Method: quicksort Memory: 480kB
-> HashAggregate (cost=3308.91..3371.45 rows=5003 width=19)
(actual time=1530.276..1540.206 rows=5003 loops=1)
-> Nested Loop (cost=0.00..1708.29 rows=128050 width=19)
(actual time=0.264..1034.048 rows=198333 loops=1)
-> Seq Scan on history t1 (cost=0.00..78.03 rows=5003
width=15) (actual time=0.077..8.835 rows=5003 loops=1)
-> Index Scan using fxxx on history t2
(cost=0.00..0.31 rows=1 width=11) (actual time=0.017..0.087 rows=40
loops=5003)
Index Cond: (((t2.product)::text =
(t1.product)::text) AND (t1.id >= t2.id))
Total runtime: 1567.125 ms
(9 rows)
postgres=# explain analyze SELECT sale_date, product, sale_price,
COALESCE((SELECT SUM(sale_price)
FROM history
WHERE product = o.product
AND id <= o.id), 0) AS total
FROM history o;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on history o (cost=0.00..41532.29 rows=5003 width=15)
(actual time=0.073..825.333 rows=5003 loops=1)
SubPlan
-> Aggregate (cost=8.28..8.29 rows=1 width=4) (actual
time=0.158..0.159 rows=1 loops=5003)
-> Index Scan using fxxx on history (cost=0.00..8.27
rows=1 width=4) (actual time=0.018..0.086 rows=40 loops=5003)
Index Cond: (((product)::text = ($0)::text) AND (id <= $1))
Total runtime: 833.213 ms
(6 rows)
postgres=# show effective_cache_size ;
effective_cache_size
----------------------
600MB
(1 row)
postgres=# SHOW shared_buffers ;
shared_buffers
----------------
300MB
(1 row)
Maybe I have too big random_page_cost?
postgres=# SHOW random_page_cost ;
random_page_cost
------------------
4
(1 row)
Time: 0,351 ms
postgres=# set random_page_cost to 2;
SET
Time: 0,330 ms
postgres=# SHOW random_page_cost ;
random_page_cost
------------------
2
(1 row)
Time: 0,320 ms
postgres=# explain analyze SELECT sale_date, product, sale_price,
COALESCE((SELECT SUM(sale_price)
FROM history
WHERE product = o.product
AND id <= o.id), 0) AS total
FROM history o;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on history o (cost=0.00..21518.09 rows=5003 width=15)
(actual time=0.132..809.701 rows=5003 loops=1)
SubPlan
-> Aggregate (cost=4.28..4.29 rows=1 width=4) (actual
time=0.154..0.155 rows=1 loops=5003)
-> Index Scan using fxxx on history (cost=0.00..4.27
rows=1 width=4) (actual time=0.020..0.088 rows=40 loops=5003)
Index Cond: (((product)::text = ($0)::text) AND (id <= $1))
Total runtime: 817.358 ms
Regards
Pavel Stehule
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
