explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NPZr

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 1,195.202 ↑ 1.0 20 1

Limit (cost=286,475.27..286,475.32 rows=20 width=740) (actual time=1,195.197..1,195.202 rows=20 loops=1)

  • Output: (COALESCE("order".partner_id, web_stat.partner_id)), (COALESCE("order".webmaster, web_stat.webmaster)), (COALESCE("order".offer_id, web_stat.offer_id)), (count(*)), (COALESCE(sum(web_stat.orders_count), '0'::bigint)), (COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)), (sum((("order".partner_id IS NULL))::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".partner_id IS NULL))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum(web_stat.orders_history_count)), (CASE WHEN ((COALESCE(sum(web_stat.orders_count), '0'::bigint)) <> 0) THEN (round((((sum(web_stat.orders_history_count)))::numeric / ((COALESCE(sum(web_stat.orders_count), '0'::bigint)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum((("order".session_id IS NOT NULL))::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".session_id IS NOT NULL))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum(("order".false_track_id)::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum(("order".false_track_id)::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum((("order".status = 2))::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".status = 2))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (CASE WHEN ((COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)) <> 0) THEN (round((((COALESCE(sum(web_stat.orders_count), '0'::bigint)))::numeric / ((COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum(CASE WHEN ("order".status = 2) THEN "order".marketing_cost ELSE '0'::numeric END)), (sum(CASE WHEN ("order".status = 1) THEN "order".marketing_cost ELSE '0'::numeric END)), (sum(CASE WHEN ("order".final AND ("order".status = 2)) THEN "order".marketing_cost ELSE '0'::numeric END)), (CASE WHEN ((sum((("order".status = 2))::integer)) <> 0) THEN (round(((sum(CASE WHEN ("order".status = 2) THEN "order".marketing_cost ELSE '0'::numeric END)) / ((sum((("order".status = 2))::integer)))::numeric), 4))::double precision ELSE NULL::double precision END)
2. 0.048 1,195.197 ↑ 3,689.6 20 1

Sort (cost=286,475.27..286,659.75 rows=73,791 width=740) (actual time=1,195.196..1,195.197 rows=20 loops=1)

  • Output: (COALESCE("order".partner_id, web_stat.partner_id)), (COALESCE("order".webmaster, web_stat.webmaster)), (COALESCE("order".offer_id, web_stat.offer_id)), (count(*)), (COALESCE(sum(web_stat.orders_count), '0'::bigint)), (COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)), (sum((("order".partner_id IS NULL))::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".partner_id IS NULL))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum(web_stat.orders_history_count)), (CASE WHEN ((COALESCE(sum(web_stat.orders_count), '0'::bigint)) <> 0) THEN (round((((sum(web_stat.orders_history_count)))::numeric / ((COALESCE(sum(web_stat.orders_count), '0'::bigint)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum((("order".session_id IS NOT NULL))::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".session_id IS NOT NULL))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum(("order".false_track_id)::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum(("order".false_track_id)::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum((("order".status = 2))::integer)), (CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".status = 2))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END), (CASE WHEN ((COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)) <> 0) THEN (round((((COALESCE(sum(web_stat.orders_count), '0'::bigint)))::numeric / ((COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)))::numeric), 4))::double precision ELSE NULL::double precision END), (sum(CASE WHEN ("order".status = 2) THEN "order".marketing_cost ELSE '0'::numeric END)), (sum(CASE WHEN ("order".status = 1) THEN "order".marketing_cost ELSE '0'::numeric END)), (sum(CASE WHEN ("order".final AND ("order".status = 2)) THEN "order".marketing_cost ELSE '0'::numeric END)), (CASE WHEN ((sum((("order".status = 2))::integer)) <> 0) THEN (round(((sum(CASE WHEN ("order".status = 2) THEN "order".marketing_cost ELSE '0'::numeric END)) / ((sum((("order".status = 2))::integer)))::numeric), 4))::double precision ELSE NULL::double precision END)
  • Sort Key: (count(*)) DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 32kB
3. 0.386 1,195.149 ↑ 1,101.4 67 1

Hash Full Join (cost=270,096.69..284,511.72 rows=73,791 width=740) (actual time=1,054.825..1,195.149 rows=67 loops=1)

  • Output: COALESCE("order".partner_id, web_stat.partner_id), COALESCE("order".webmaster, web_stat.webmaster), COALESCE("order".offer_id, web_stat.offer_id), (count(*)), (COALESCE(sum(web_stat.orders_count), '0'::bigint)), (COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)), (sum((("order".partner_id IS NULL))::integer)), CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".partner_id IS NULL))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END, (sum(web_stat.orders_history_count)), CASE WHEN ((COALESCE(sum(web_stat.orders_count), '0'::bigint)) <> 0) THEN (round((((sum(web_stat.orders_history_count)))::numeric / ((COALESCE(sum(web_stat.orders_count), '0'::bigint)))::numeric), 4))::double precision ELSE NULL::double precision END, (sum((("order".session_id IS NOT NULL))::integer)), CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".session_id IS NOT NULL))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END, (sum(("order".false_track_id)::integer)), CASE WHEN ((count(*)) <> 0) THEN (round((((sum(("order".false_track_id)::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END, (sum((("order".status = 2))::integer)), CASE WHEN ((count(*)) <> 0) THEN (round((((sum((("order".status = 2))::integer)))::numeric / ((count(*)))::numeric), 4))::double precision ELSE NULL::double precision END, CASE WHEN ((COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)) <> 0) THEN (round((((COALESCE(sum(web_stat.orders_count), '0'::bigint)))::numeric / ((COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)))::numeric), 4))::double precision ELSE NULL::double precision END, (sum(CASE WHEN ("order".status = 2) THEN "order".marketing_cost ELSE '0'::numeric END)), (sum(CASE WHEN ("order".status = 1) THEN "order".marketing_cost ELSE '0'::numeric END)), (sum(CASE WHEN ("order".final AND ("order".status = 2)) THEN "order".marketing_cost ELSE '0'::numeric END)), CASE WHEN ((sum((("order".status = 2))::integer)) <> 0) THEN (round(((sum(CASE WHEN ("order".status = 2) THEN "order".marketing_cost ELSE '0'::numeric END)) / ((sum((("order".status = 2))::integer)))::numeric), 4))::double precision ELSE NULL::double precision END
  • Inner Unique: true
  • Hash Cond: (("order".partner_id = web_stat.partner_id) AND (("order".webmaster)::text = (web_stat.webmaster)::text) AND ("order".offer_id = web_stat.offer_id))
4. 132.521 1,125.948 ↑ 1,171.3 63 1

GroupAggregate (cost=231,931.86..237,464.30 rows=73,791 width=151) (actual time=985.984..1,125.948 rows=63 loops=1)

  • Output: "order".partner_id, "order".webmaster, "order".offer_id, count(*), sum((("order".partner_id IS NULL))::integer), sum((("order".session_id IS NOT NULL))::integer), sum(("order".false_track_id)::integer), sum((("order".status = 2))::integer), sum(CASE WHEN ("order".status = 2) THEN "order".marketing_cost ELSE '0'::numeric END), sum(CASE WHEN ("order".status = 1) THEN "order".marketing_cost ELSE '0'::numeric END), sum(CASE WHEN ("order".final AND ("order".status = 2)) THEN "order".marketing_cost ELSE '0'::numeric END)
  • Group Key: "order".partner_id, "order".webmaster, "order".offer_id
5. 140.313 993.427 ↓ 3.1 263,961 1

Sort (cost=231,931.86..232,143.91 rows=84,822 width=55) (actual time=958.734..993.427 rows=263,961 loops=1)

  • Output: "order".partner_id, "order".webmaster, "order".offer_id, "order".session_id, "order".false_track_id, "order".status, "order".marketing_cost, "order".final
  • Sort Key: "order".offer_id
  • Sort Method: external merge Disk: 17904kB
6. 747.526 853.114 ↓ 3.1 263,961 1

Bitmap Heap Scan on public."order" (cost=21,204.40..222,086.76 rows=84,822 width=55) (actual time=115.648..853.114 rows=263,961 loops=1)

  • Output: "order".partner_id, "order".webmaster, "order".offer_id, "order".session_id, "order".false_track_id, "order".status, "order".marketing_cost, "order".final
  • Recheck Cond: ((("order".webmaster)::text = '90289'::text) AND ("order".partner_id = 2))
  • Rows Removed by Index Recheck: 653277
  • Heap Blocks: exact=48464 lossy=99456
7. 4.396 105.588 ↓ 0.0 0 1

BitmapAnd (cost=21,204.40..21,204.40 rows=84,822 width=0) (actual time=105.588..105.588 rows=0 loops=1)

8. 35.001 35.001 ↓ 1.0 263,984 1

Bitmap Index Scan on order_webmaster_idx (cost=0.00..5,088.52 rows=263,478 width=0) (actual time=35.001..35.001 rows=263,984 loops=1)

  • Index Cond: (("order".webmaster)::text = '90289'::text)
9. 66.191 66.191 ↓ 1.0 872,177 1

Bitmap Index Scan on order_partner_id_idx (cost=0.00..16,073.22 rows=869,972 width=0) (actual time=66.191..66.191 rows=872,177 loops=1)

  • Index Cond: ("order".partner_id = 2)
10. 0.023 68.815 ↑ 124.8 67 1

Hash (cost=38,018.51..38,018.51 rows=8,361 width=38) (actual time=68.815..68.815 rows=67 loops=1)

  • Output: web_stat.partner_id, web_stat.webmaster, web_stat.offer_id, (COALESCE(sum(web_stat.orders_count), '0'::bigint)), (COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint)), (sum(web_stat.orders_history_count))
  • Buckets: 16384 Batches: 1 Memory Usage: 133kB
11. 12.482 68.792 ↑ 124.8 67 1

HashAggregate (cost=37,851.29..37,934.90 rows=8,361 width=38) (actual time=68.740..68.792 rows=67 loops=1)

  • Output: web_stat.partner_id, web_stat.webmaster, web_stat.offer_id, COALESCE(sum(web_stat.orders_count), '0'::bigint), COALESCE(sum(GREATEST(web_stat.sessions, web_stat.ga_sessions, web_stat.redirects)), '0'::bigint), sum(web_stat.orders_history_count)
  • Group Key: web_stat.partner_id, web_stat.webmaster, web_stat.offer_id
12. 54.245 56.310 ↓ 2.3 19,334 1

Bitmap Heap Scan on public.web_stat (cost=350.91..37,702.79 rows=8,486 width=34) (actual time=3.839..56.310 rows=19,334 loops=1)

  • Output: web_stat.id, web_stat.date, web_stat.offer_id, web_stat.partner_id, web_stat.webmaster, web_stat.sessions, web_stat.uniqs, web_stat.iframe, web_stat.search_refs_count, web_stat.small_screen, web_stat.fraud, web_stat.sb30, web_stat.sb60, web_stat.sb300, web_stat.sb900, web_stat.sb1800, web_stat.sb3600, web_stat.sd0m, web_stat.sd1m, web_stat.sd2m, web_stat.sd5m, web_stat.sb10, web_stat.sb_med, web_stat.sd_med, web_stat.depth_med, web_stat.max_clicks_dups, web_stat.clicks_uniqs, web_stat.ga_sessions, web_stat.ga_br, web_stat.orders_count, web_stat.orders_iframe_count, web_stat.orders_small_screen_count, web_stat.orders_search_refs_count, web_stat.orders_history_count, web_stat.orders_sb10_count, web_stat.orders_sb30_count, web_stat.orders_sb60_count, web_stat.orders_sb300_count, web_stat.orders_sb900_count, web_stat.orders_sb1800_count, web_stat.orders_sb3600_count, web_stat.orders_sb_med, web_stat.orders_click_time_med, web_stat.orders_sessions_uniqs, web_stat.orders_sessions_count, web_stat.orders_clicks_uniqs, web_stat.orders_clicks_count, web_stat.orders_max_sessions_dups, web_stat.orders_max_clicks_dups, web_stat.redirects, web_stat.redirects_uniqs
  • Recheck Cond: ((web_stat.webmaster)::text = '90289'::text)
  • Filter: (web_stat.partner_id = 2)
  • Rows Removed by Filter: 16
  • Heap Blocks: exact=13563
13. 2.065 2.065 ↓ 1.0 19,350 1

Bitmap Index Scan on web_stat_webmaster_idx (cost=0.00..348.79 rows=18,714 width=0) (actual time=2.065..2.065 rows=19,350 loops=1)

  • Index Cond: ((web_stat.webmaster)::text = '90289'::text)