Advertisement
Guest User

Untitled

a guest
Feb 26th, 2020
193
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.97 KB | None | 0 0
  1. Old query, jsonb:
  2. QUERY PLAN
  3. ----------------------------------------------------------------------------------------------------------------------------------
  4. Nested Loop (cost=0.48..1359.15 rows=2714 width=168) (actual time=0.067..41.844 rows=2476 loops=1)
  5. -> Seq Scan on users (cost=0.00..40.14 rows=2714 width=309) (actual time=0.019..1.851 rows=2476 loops=1)
  6. -> Aggregate (cost=0.48..0.48 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=2476)
  7. -> Function Scan on jsonb_each_text tmp (cost=0.00..0.48 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=2476)
  8. Filter: ((val = 'true'::text) AND (col = ANY ('{premium,pepsi,spawnmason,staff,developer}'::text[])))
  9. Rows Removed by Filter: 13
  10. Planning Time: 0.156 ms
  11. Execution Time: 42.072 ms
  12. (8 rows)
  13.  
  14. Your first suggestion, with VALUES:
  15. QUERY PLAN
  16. -------------------------------------------------------------------------------------------------------------------
  17. Seq Scan on users (cost=0.00..87.64 rows=2714 width=168) (actual time=0.026..7.057 rows=2476 loops=1)
  18. SubPlan 1
  19. -> Values Scan on "*VALUES*" (cost=0.00..0.02 rows=2 width=32) (actual time=0.000..0.002 rows=1 loops=2476)
  20. Filter: column2
  21. Rows Removed by Filter: 4
  22. Planning Time: 0.146 ms
  23. Execution Time: 7.166 ms
  24. (7 rows)
  25.  
  26. Your last suggestion, with string_to_array:
  27. QUERY PLAN
  28. --------------------------------------------------------------------------------------------------------
  29. Seq Scan on users (cost=0.00..42.86 rows=2714 width=168) (actual time=0.021..1.816 rows=2476 loops=1)
  30. Planning Time: 0.071 ms
  31. Execution Time: 1.945 ms
  32. (3 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement