Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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) ;
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

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

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

ANALYZE ;
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=10954.29..11147.45 rows=15914 width=47) (actual time=148.641..154.023 rows=11136 loops=1)

  ->  Sort  (cost=10954.29..11002.58 rows=19316 width=47) (actual time=148.630..150.560 rows=19158 loops=1)

        Sort Key: employees.connections DESC, employees.employee_id, employees.something_else

        Sort Method: quicksort  Memory: 1667kB

        ->  Hash Join  (cost=1778.92..9579.23 rows=19316 width=47) (actual time=24.714..130.133 rows=19158 loops=1)

              Hash Cond: (employee_companies.employee_id = employees.employee_id)

              ->  Hash Join  (cost=1293.00..8827.31 rows=19421 width=4) (actual time=19.234..113.972 rows=19257 loops=1)

                    Hash Cond: (companies.company_id = employee_companies.company_id)

                    ->  Seq Scan on companies  (cost=0.00..5924.00 rows=161840 width=4) (actual time=0.023..60.569 rows=160326 loops=1)

                          Filter: (website IS NOT NULL)

                          Rows Removed by Filter: 239674

                    ->  Hash  (cost=693.00..693.00 rows=48000 width=8) (actual time=18.875..18.875 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.011..9.488 rows=48000 loops=1)

                                Filter: (employee_id IS NOT NULL)

              ->  Hash  (cost=287.00..287.00 rows=15914 width=47) (actual time=5.461..5.461 rows=15914 loops=1)

                    Buckets: 16384  Batches: 1  Memory Usage: 874kB

                    ->  Seq Scan on employees  (cost=0.00..287.00 rows=15914 width=47) (actual time=0.022..3.430 rows=15914 loops=1)

                          Filter: (country = 'Uruguay'::text)

                          Rows Removed by Filter: 86

Planning time: 1.557 ms

Execution time: 155.187 ms


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=10618.80..10654.62 rows=14326 width=47) (actual time=124.616..125.607 rows=11136 loops=1)

  Sort Key: employees.connections DESC

  Sort Method: quicksort  Memory: 907kB

  ->  Hash Semi Join  (cost=9070.07..9629.85 rows=14326 width=47) (actual time=111.517..120.777 rows=11136 loops=1)

        Hash Cond: (employees.employee_id = employee_companies.employee_id)

        ->  Seq Scan on employees  (cost=0.00..287.00 rows=15914 width=47) (actual time=0.019..3.769 rows=15914 loops=1)

              Filter: (country = 'Uruguay'::text)

              Rows Removed by Filter: 86

        ->  Hash  (cost=8827.31..8827.31 rows=19421 width=4) (actual time=111.454..111.454 rows=19257 loops=1)

              Buckets: 32768  Batches: 1  Memory Usage: 934kB

              ->  Hash Join  (cost=1293.00..8827.31 rows=19421 width=4) (actual time=17.837..106.089 rows=19257 loops=1)

                    Hash Cond: (companies.company_id = employee_companies.company_id)

                    ->  Seq Scan on companies  (cost=0.00..5924.00 rows=161840 width=4) (actual time=0.014..56.914 rows=160326 loops=1)

                          Filter: (website IS NOT NULL)

                          Rows Removed by Filter: 239674

                    ->  Hash  (cost=693.00..693.00 rows=48000 width=8) (actual time=17.567..17.567 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.013..7.611 rows=48000 loops=1)

Planning time: 0.661 ms

Execution time: 126.453 ms