rueian

%{
    #include<stdio.h>
    #include<string.h>
    #include<stdlib.h>
    #include<time.h>
    #include "y.tab.h"
    void yyerror(char *);
%}

%%

rueian

#!/bin/bash

# ssh-multi : a script to ssh multiple servers over multiple tmux panes
# nomad-fr : https://gist.github.com/nomad-fr/8f2316c24c9d488a603b
# Based on D.Kovalov work : https://gist.github.com/dmytro/3984680

# config
#user=$USER # user use for ssh connection
user=root

rueian

postgres=# 
EXPLAIN WITH subscribers AS MATERIALIZED (SELECT user_id FROM playlist_subscriptions WHERE list_id = 3343594)
SELECT * FROM devices WHERE user_id IN (SELECT user_id FROM subscribers);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=33227.59..58222.98 rows=294587 width=170)
   CTE subscribers
     ->  Bitmap Heap Scan on playlist_subscriptions  (cost=1350.13..32705.43 rows=23188 width=8)
           Recheck Cond: (list_id = 3343594)
           ->  Bitmap Index Scan on playlist_subscriptions_list_id_user_id_uniq  (cost=0.00..1344.34 rows=23188 width=0)

rueian

postgres=# 
EXPLAIN WITH subscribers AS MATERIALIZED (SELECT user_id FROM playlist_subscriptions WHERE list_id = 3343594)
SELECT * FROM devices WHERE user_id IN (SELECT user_id FROM subscribers);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=33677.16..245826.56 rows=294587 width=170)
   Hash Cond: (devices.user_id = subscribers.user_id)
   CTE subscribers
     ->  Bitmap Heap Scan on playlist_subscriptions  (cost=1350.13..32705.43 rows=23188 width=8)
           Recheck Cond: (list_id = 3343594)

rueian

postgres=# SET enable_hashjoin=off;
SET
postgres=# EXPLAIN WITH subscribers AS MATERIALIZED (SELECT user_id FROM playlist_subscriptions WHERE list_id = 3343594)
           SELECT * FROM devices WHERE user_id IN (SELECT user_id FROM subscribers);
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=33227.59..1384775.03 rows=294587 width=170)
   CTE subscribers
     ->  Bitmap Heap Scan on playlist_subscriptions  (cost=1350.13..32705.43 rows=23188 width=8)
           Recheck Cond: (list_id = 3343594)

rueian

postgres=# 
EXPLAIN WITH subscribers AS (SELECT user_id FROM playlist_subscriptions WHERE list_id = 3343594)
SELECT * FROM devices WHERE user_id IN (SELECT user_id FROM subscribers);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=32995.28..241867.55 rows=294585 width=170)
   Hash Cond: (devices.user_id = playlist_subscriptions.user_id)
   ->  Seq Scan on devices  (cost=0.00..194390.62 rows=5516762 width=170)
   ->  Hash  (cost=32705.43..32705.43 rows=23188 width=8)
         ->  Bitmap Heap Scan on playlist_subscriptions  (cost=1350.13..32705.43 rows=23188 width=8)

rueian

postgres=# 
EXPLAIN SELECT * FROM devices WHERE user_id IN (SELECT user_id FROM playlist_subscriptions WHERE list_id = 3343594);
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=32995.28..241867.55 rows=294585 width=170)
   Hash Cond: (devices.user_id = playlist_subscriptions.user_id)
   ->  Seq Scan on devices  (cost=0.00..194390.62 rows=5516762 width=170)
   ->  Hash  (cost=32705.43..32705.43 rows=23188 width=8)
         ->  Bitmap Heap Scan on playlist_subscriptions  (cost=1350.13..32705.43 rows=23188 width=8)
               Recheck Cond: (list_id = 3343594)

rueian

postgres=# 
EXPLAIN SELECT devices.* FROM devices JOIN playlist_subscriptions ON devices.user_id = playlist_subscriptions.user_id WHERE list_id = 3343594;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=32995.28..241867.55 rows=294585 width=170)
   Hash Cond: (devices.user_id = playlist_subscriptions.user_id)
   ->  Seq Scan on devices  (cost=0.00..194390.62 rows=5516762 width=170)
   ->  Hash  (cost=32705.43..32705.43 rows=23188 width=8)
         ->  Bitmap Heap Scan on playlist_subscriptions  (cost=1350.13..32705.43 rows=23188 width=8)
               Recheck Cond: (list_id = 3343594)

rueian

CREATE TABLE IF NOT EXISTS counters (
 id TEXT PRIMARY KEY COLLATE "C",
 value BIGINT
);

rueian

postgres=# explain analyze select * from counters where id like 'ms:149895:%';
 QUERY PLAN
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
 Index Scan using counters_pkey on counters (cost=0.56..8.58 rows=488 width=36) (actual time=0.014..0.022 rows=10 loops=1)
 Index Cond: ((id >= 'ms:149895:'::text) AND (id < 'ms:149895;'::text))
 Filter: (id ~~ 'ms:149895:%'::text)
 Planning Time: 0.148 ms
 Execution Time: 0.034 ms
(5 rows)