3 ms·

If the author is reading please post the EXPLAIN ANALYZE results for these queries. I'd much prefer the raw text psql output to the GUI representation in pgAdmi

by sehrope 13y ago

If the author is reading please post the EXPLAIN ANALYZE results for these queries. I'd much prefer the raw text psql output to the GUI representation in pgAdmin. More importantly it has the actual numbers for the intermediate steps to see which is the culprit. Also, any idea how many times each variation was executed? 10M rows is not that big. At 1K per row (which is probably an overestimate) that comes out to 100 MB. That easily fits in memory. After the first full scan all of it would come from the cache. It's not a fair comparison unless you flush the cache between tests.

hglaser 13y ago
OP here. It's no EXPLAIN ANALYZE, but here are the EXPLAINs at least. The originals were averages of 10 runs. These EXPLAINs are just quick runs on my laptop, so no promises. :) First query: QUERY PLAN ----------------------------------------------------------------------------------------------------- Sort (cost=3475858.60..3475859.56 rows=385 width=22) Sort Key: (count(DISTINCT time_on_site_logs.user_id)) -> GroupAggregate (cost=3370756.94..3475842.06 rows=385 width=22) -> Sort (cost=3370756.94..3405784.03 rows=14010837 width=22) Sort Key: dashboards.name -> Hash Join (cost=44.90..558337.80 rows=14010837 width=22) Hash Cond: (time_on_site_logs.dashboard_id = dashboards.id) -> Seq Scan on time_on_site_logs (cost=0.00..365621.11 rows=14016911 width=8) -> Hash (cost=29.40..29.40 rows=1240 width=22) -> Seq Scan on dashboards (cost=0.00..29.40 rows=1240 width=22) (10 rows) Second Query: QUERY PLAN ----------------------------------------------------------------------------------------------------- Sort (cost=2709630.36..2709631.09 rows=291 width=26) Sort Key: (count(DISTINCT time_on_site_logs.user_id)) -> Merge Join (cost=2604392.06..2709618.45 rows=291 width=26) Merge Cond: (time_on_site_logs.dashboard_id = dashboards.id) -> GroupAggregate (cost=2604392.06..2709521.80 rows=291 width=8) -> Sort (cost=2604392.06..2639434.34 rows=14016911 width=8) Sort Key: time_on_site_logs.dashboard_id -> Seq Scan on time_on_site_logs (cost=0.00..365621.11 rows=14016911 width=8) -> Index Scan using dashboards_pkey on dashboards (cost=0.00..87.00 rows=1240 width=22) Third Query: QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Sort (cost=436381.86..436382.36 rows=200 width=26) Sort Key: log_counts.ct -> Hash Join (cost=436308.72..436374.22 rows=200 width=26) Hash Cond: (dashboards.id = log_counts.dashboard_id) -> Seq Scan on dashboards (cost=0.00..29.40 rows=1240 width=22) -> Hash (cost=436306.22..436306.22 rows=200 width=12) -> Subquery Scan on log_counts (cost=436302.22..436306.22 rows=200 width=12) -> HashAggregate (cost=436302.22..436304.22 rows=200 width=4) -> HashAggregate (cost=435705.66..435944.28 rows=23862 width=8) -> Seq Scan on time_on_site_logs (cost=0.00..365621.11 rows=14016911 width=8)
- nerfhammer 13y ago
  Why is it all hash and merge joins? No indexes? I think I know another way to speed up your queries...

by sehrope 13y ago

hglaser 13y ago

OP here. It's no EXPLAIN ANALYZE, but here are the EXPLAINs at least. The originals were averages of 10 runs. These EXPLAINs are just quick runs on my laptop, so no promises. :) First query: QUERY PLAN ----------------------------------------------------------------------------------------------------- Sort (cost=3475858.60..3475859.56 rows=385 width=22) Sort Key: (count(DISTINCT time_on_site_logs.user_id)) -> GroupAggregate (cost=3370756.94..3475842.06 rows=385 width=22) -> Sort (cost=3370756.94..3405784.03 rows=14010837 width=22) Sort Key: dashboards.name -> Hash Join (cost=44.90..558337.80 rows=14010837 width=22) Hash Cond: (time_on_site_logs.dashboard_id = dashboards.id) -> Seq Scan on time_on_site_logs (cost=0.00..365621.11 rows=14016911 width=8) -> Hash (cost=29.40..29.40 rows=1240 width=22) -> Seq Scan on dashboards (cost=0.00..29.40 rows=1240 width=22) (10 rows) Second Query: QUERY PLAN ----------------------------------------------------------------------------------------------------- Sort (cost=2709630.36..2709631.09 rows=291 width=26) Sort Key: (count(DISTINCT time_on_site_logs.user_id)) -> Merge Join (cost=2604392.06..2709618.45 rows=291 width=26) Merge Cond: (time_on_site_logs.dashboard_id = dashboards.id) -> GroupAggregate (cost=2604392.06..2709521.80 rows=291 width=8) -> Sort (cost=2604392.06..2639434.34 rows=14016911 width=8) Sort Key: time_on_site_logs.dashboard_id -> Seq Scan on time_on_site_logs (cost=0.00..365621.11 rows=14016911 width=8) -> Index Scan using dashboards_pkey on dashboards (cost=0.00..87.00 rows=1240 width=22) Third Query: QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Sort (cost=436381.86..436382.36 rows=200 width=26) Sort Key: log_counts.ct -> Hash Join (cost=436308.72..436374.22 rows=200 width=26) Hash Cond: (dashboards.id = log_counts.dashboard_id) -> Seq Scan on dashboards (cost=0.00..29.40 rows=1240 width=22) -> Hash (cost=436306.22..436306.22 rows=200 width=12) -> Subquery Scan on log_counts (cost=436302.22..436306.22 rows=200 width=12) -> HashAggregate (cost=436302.22..436304.22 rows=200 width=4) -> HashAggregate (cost=435705.66..435944.28 rows=23862 width=8) -> Seq Scan on time_on_site_logs (cost=0.00..365621.11 rows=14016911 width=8)

nerfhammer 13y ago
Why is it all hash and merge joins? No indexes? I think I know another way to speed up your queries...