SHARE
TWEET

Untitled

a guest Apr 7th, 2017 136 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Limit  (cost=316926.14..316926.39 rows=100 width=20) (actual time=2326.812..2326.830 rows=100 loops=1)
  2.   ->  Sort  (cost=316926.14..317014.83 rows=35476 width=20) (actual time=2326.809..2326.820 rows=100 loops=1)
  3.         Sort Key: (count(p.id)) DESC
  4.         Sort Method: top-N heapsort  Memory: 32kB
  5.         ->  HashAggregate  (cost=315215.51..315570.27 rows=35476 width=20) (actual time=2311.296..2321.739 rows=34608 loops=1)
  6.               Group Key: u.id
  7.               ->  Hash Join  (cost=1176.89..308201.88 rows=1402727 width=16) (actual time=16.538..1784.546 rows=1910831 loops=1)
  8.                     Hash Cond: (p.user_id = u.id)
  9.                     ->  Seq Scan on posts p  (cost=0.00..286185.34 rows=1816634 width=8) (actual time=0.103..1144.681 rows=2173916 loops=1)
  10.                     ->  Hash  (cost=733.44..733.44 rows=35476 width=12) (actual time=15.763..15.763 rows=34609 loops=1)
  11.                           Buckets: 65536  Batches: 1  Memory Usage: 2021kB
  12.                           ->  Seq Scan on users u  (cost=0.00..733.44 rows=35476 width=12) (actual time=0.033..6.521 rows=34609 loops=1)
  13.                                 Filter: (username IS NOT NULL)
  14.                                 Rows Removed by Filter: 11335
  15.  
  16.  
  17. With set enable_seqscan = false even worse:
  18.  
  19. Limit  (cost=1160881.74..1160881.99 rows=100 width=20) (actual time=2758.086..2758.107 rows=100 loops=1)
  20.   ->  Sort  (cost=1160881.74..1160970.43 rows=35476 width=20) (actual time=2758.084..2758.098 rows=100 loops=1)
  21.         Sort Key: (count(p.id)) DESC
  22.         Sort Method: top-N heapsort  Memory: 32kB
  23.         ->  GroupAggregate  (cost=0.79..1159525.87 rows=35476 width=20) (actual time=0.095..2749.859 rows=34608 loops=1)
  24.               Group Key: u.id
  25.               ->  Merge Join  (cost=0.79..1152157.48 rows=1402727 width=16) (actual time=0.036..2537.064 rows=1910831 loops=1)
  26.                     Merge Cond: (u.id = p.user_id)
  27.                     ->  Index Scan using users_pkey on users u  (cost=0.29..2404.83 rows=35476 width=12) (actual time=0.016..41.163 rows=34609 loops=1)
  28.                           Filter: (username IS NOT NULL)
  29.                           Rows Removed by Filter: 11335
  30.                     ->  Index Scan using posts_user_id_index on posts p  (cost=0.43..1131472.19 rows=1816634 width=8) (actual time=0.012..2191.856 rows=2173916 loops=1)
  31. Planning time: 1.281 ms
  32. Execution time: 2758.187 ms
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top