Table layouts:
historical
Column | Type | Modifiers
-----------+--------------+------------------------------------------------------------------
record_id | integer | not null default nextval('historical_record_id_seq'::regclass)
f3 | integer | not null
date | date | not null
f4 | smallint |
f5 | numeric(9,2) | not null
join_id | integer | not null
Indexes:
"historical_f3" btree (f3) WITH (fillfactor=98), tablespace "st2"
"historical_join_id_date" btree (join_id, date) WITH (fillfactor=98),
tablespace "st2"
"historical_record_id" btree (record_id) WITH (fillfactor=98),
tablespace "st2"
join_ids
Column | Type | Modifiers
------------+---------+-----------
join_id | integer | not null
customer_id | integer | not null
Indexes:
"join_ids_pkey" PRIMARY KEY, btree (join_id)
"join_ids_customerid_joinid" btree (customer_id, join_id) WITH
(fillfactor=98)
Historical has 215 million rows.
join_ids has 2.5 million rows.
The number or rows in join_ids that have a matching record in historical
(by join_id) is roughly 86 million.
The select from the previous 3 explain analyze had about 0.22% (that is 1/5
of 1%... not 22%) rows that needed to be returned for that query from the
historical table.
