search

powa-team / powa-web

PoWA user interface
http://powa.readthedocs.io/
73 stars 31 forks source link

Add clause on srvid #106

Closed anayrat closed 4 years ago

anayrat commented 4 years ago

This allows to use indexes to perform the join instead of joining and filter after.

Before:

>  Nested Loop Semi Join  (cost=0.41..6217.26 rows=1 width=553) (actual time=119.577..19930.485 rows=376 loops=3)
     Join Filter: (psh.queryid = powa_statements_1.queryid)
     Rows Removed by Join Filter: 3280129
     Buffers: shared hit=3125260
     ->  Index Scan using powa_statements_history_query_ts on powa_statements_history psh  (cost=0.41..8.43 rows=1 width=553) (actual time=78.585..123.357 rows=376 loops=3)
           Index Cond: ((srvid = 14) AND (coalesce_range && '["2020-07-01 07:41:27+00","2020-07-01 18:07:16+00"]'::tstzrange))
           Filter: (dbid = powa_databases.oid)
           Rows Removed by Filter: 9386
           Buffers: shared hit=31758
     ->  Seq Scan on powa_statements powa_statements_1  (cost=0.00..6129.54 rows=6343 width=8) (actual time=16.600..38.088 rows=8717 loops=1129)
           Filter: (dbid = powa_databases.oid)
           Rows Removed by Filter: 155438
           Buffers: shared hit=3093502

After:

->  Nested Loop Semi Join  (cost=0.83..24.90 rows=1 width=553) (actual time=81.412..130.928 rows=376 loops=3)
      Buffers: shared hit=36274
      ->  Index Scan using powa_statements_history_query_ts on powa_statements_history psh  (cost=0.41..8.43 rows=1 width=553) (actual time=81.391..124.578 rows=376 loops=3)
            Index Cond: ((srvid = 14) AND (coalesce_range && '["2020-07-01 07:41:27+00","2020-07-01 18:07:16+00"]'::tstzrange))
            Filter: (dbid = powa_databases.oid)
            Rows Removed by Filter: 9386
            Buffers: shared hit=31758
      ->  Index Only Scan using powa_statements_pkey on powa_statements powa_statements_1  (cost=0.42..8.44 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1129)
            Index Cond: ((srvid = 14) AND (queryid = psh.queryid) AND (dbid = powa_databases.oid))
            Heap Fetches: 1129
            Buffers: shared hit=4516

I applied the same fix for powa_base_waitdata_detailed_db