clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 462237 distinct fiddles created so far.

CREATE TABLE employees ( employee_id integer PRIMARY KEY, country text, connections integer, something_else text ) ; CREATE INDEX idx_employee_country ON employees (country) ; CREATE TABLE companies ( company_id integer PRIMARY KEY, website text, something_else text ) ; CREATE INDEX not_empty_websites ON companies(company_id, website) WHERE website IS NOT NULL ; CREATE TABLE employee_companies ( employee_id integer NOT NULL REFERENCES employees(employee_id), company_id integer NOT NULL REFERENCES companies(company_id), PRIMARY KEY (employee_id, company_id) ) ; CREATE INDEX company_employee ON employee_companies(company_id, employee_id) ;
 hidden batch(es)


INSERT INTO companies (company_id, website) SELECT generate_series(1, 400000 ), /* I scale down by 25 */ case when random() < 0.4 then 'web.com' end AS website ;
400000 rows affected
 hidden batch(es)


INSERT INTO employees (employee_id, country, connections) SELECT generate_series(1, 16000), /* I scale down by 25 */ case (random()*100)::integer when 0 then 'Chile' else 'Uruguay' end AS country, (random()*10)::integer AS connections ;
16000 rows affected
 hidden batch(es)


INSERT INTO employee_companies (employee_id, company_id) SELECT DISTINCT (random()*(16000-1))::integer + 1, (random()*(400000-1))::integer + 1 FROM generate_series (1, 16000*3) ;
48000 rows affected
 hidden batch(es)


ANALYZE ;
 hidden batch(es)


EXPLAIN ANALYZE SELECT DISTINCT "employees".* FROM "employees" INNER JOIN "employee_companies" ON "employee_companies"."employee_id" = "employees"."employee_id" INNER JOIN "companies" ON "companies"."company_id" = "employee_companies"."company_id" WHERE (employee_companies.employee_id IS NOT NULL) AND (companies.website IS NOT NULL) AND (employees.country = 'Uruguay') ORDER BY employees.connections DESC
QUERY PLAN
Unique (cost=10911.87..11102.69 rows=15928 width=47) (actual time=112.835..117.072 rows=11167 loops=1)
-> Sort (cost=10911.87..10959.57 rows=19082 width=47) (actual time=112.833..114.213 rows=19192 loops=1)
Sort Key: employees.connections DESC, employees.employee_id, employees.something_else
Sort Method: quicksort Memory: 1668kB
-> Hash Join (cost=1779.10..9555.14 rows=19082 width=47) (actual time=18.015..100.598 rows=19192 loops=1)
Hash Cond: (employee_companies.employee_id = employees.employee_id)
-> Hash Join (cost=1293.00..8806.34 rows=19168 width=4) (actual time=13.679..87.387 rows=19284 loops=1)
Hash Cond: (companies.company_id = employee_companies.company_id)
-> Seq Scan on companies (cost=0.00..5924.00 rows=159733 width=4) (actual time=0.012..39.916 rows=160346 loops=1)
Filter: (website IS NOT NULL)
Rows Removed by Filter: 239654
-> Hash (cost=693.00..693.00 rows=48000 width=8) (actual time=13.428..13.428 rows=48000 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2387kB
-> Seq Scan on employee_companies (cost=0.00..693.00 rows=48000 width=8) (actual time=0.018..6.383 rows=48000 loops=1)
Filter: (employee_id IS NOT NULL)
-> Hash (cost=287.00..287.00 rows=15928 width=47) (actual time=4.316..4.316 rows=15928 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 875kB
-> Seq Scan on employees (cost=0.00..287.00 rows=15928 width=47) (actual time=0.014..2.580 rows=15928 loops=1)
Filter: (country = 'Uruguay'::text)
Rows Removed by Filter: 72
Planning time: 0.953 ms
Execution time: 117.673 ms
 hidden batch(es)


EXPLAIN ANALYZE SELECT employees.* FROM employees WHERE employee_id IN (SELECT employee_id FROM employee_companies JOIN companies ON companies.company_id = employee_companies.company_id WHERE companies.website IS NOT NULL ) AND employees.country = 'Uruguay' ORDER BY employees.connections DESC ;
QUERY PLAN
Sort (cost=10592.68..10628.44 rows=14304 width=47) (actual time=90.657..91.327 rows=11167 loops=1)
Sort Key: employees.connections DESC
Sort Method: quicksort Memory: 908kB
-> Hash Semi Join (cost=9045.94..9605.41 rows=14304 width=47) (actual time=81.776..88.264 rows=11167 loops=1)
Hash Cond: (employees.employee_id = employee_companies.employee_id)
-> Seq Scan on employees (cost=0.00..287.00 rows=15928 width=47) (actual time=0.012..2.620 rows=15928 loops=1)
Filter: (country = 'Uruguay'::text)
Rows Removed by Filter: 72
-> Hash (cost=8806.34..8806.34 rows=19168 width=4) (actual time=81.726..81.727 rows=19284 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 934kB
-> Hash Join (cost=1293.00..8806.34 rows=19168 width=4) (actual time=12.886..77.861 rows=19284 loops=1)
Hash Cond: (companies.company_id = employee_companies.company_id)
-> Seq Scan on companies (cost=0.00..5924.00 rows=159733 width=4) (actual time=0.010..38.694 rows=160346 loops=1)
Filter: (website IS NOT NULL)
Rows Removed by Filter: 239654
-> Hash (cost=693.00..693.00 rows=48000 width=8) (actual time=12.704..12.704 rows=48000 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 2387kB
-> Seq Scan on employee_companies (cost=0.00..693.00 rows=48000 width=8) (actual time=0.008..5.611 rows=48000 loops=1)
Planning time: 0.424 ms
Execution time: 91.884 ms
 hidden batch(es)