Re: Any way to favor index scans, but not bitmap index scans? - Mailing list pgsql-general

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.