Re: Incorrect index used in few cases..

On Tue, Jun 18, 2019 at 2:08 PM Tom Lane <[email protected]> wrote:
> Are you perhaps running with non-default values for any planner cost
> parameters?  Or it's not a stock build of Postgres?
>
> If you could find a way to adjust the attached example so that it
> produces the same misbehavior you see with live data, that would be
> very interesting ...

FWIW, if you move the CREATE INDEX statements before the INSERT, and
compared earlier versions of Postgres to 12, you'll see that the size
of some of the indexes are a lot smaller on 12.

v11 (representative of 9.6):

pg@tc:5411 [1067]=# \di+ i_*
                               List of relations
 Schema │          Name           │ Type  │ Owner │ Table │ Size  │ Description
────────┼─────────────────────────┼───────┼───────┼───────┼───────┼─────────────
 public │ i_agi_tc_tcn            │ index │ pg    │ tc    │ 74 MB │
 public │ i_cid_agid_tcn          │ index │ pg    │ tc    │ 82 MB │
 public │ i_tc_adid_tid           │ index │ pg    │ tc    │ 57 MB │
 public │ i_tc_advertiser_id      │ index │ pg    │ tc    │ 27 MB │
 public │ i_tc_campaign_id        │ index │ pg    │ tc    │ 28 MB │
 public │ i_tc_lud_agi            │ index │ pg    │ tc    │ 57 MB │
 public │ i_tc_uniqueness_hash_v2 │ index │ pg    │ tc    │ 21 MB │
(7 rows)

v12/master:

pg@regression:5432 [1022]=# \di+ i_*
                               List of relations
 Schema │          Name           │ Type  │ Owner │ Table │ Size  │ Description
────────┼─────────────────────────┼───────┼───────┼───────┼───────┼─────────────
 public │ i_agi_tc_tcn            │ index │ pg    │ tc    │ 69 MB │
 public │ i_cid_agid_tcn          │ index │ pg    │ tc    │ 78 MB │
 public │ i_tc_adid_tid           │ index │ pg    │ tc    │ 36 MB │
 public │ i_tc_advertiser_id      │ index │ pg    │ tc    │ 20 MB │
 public │ i_tc_campaign_id        │ index │ pg    │ tc    │ 24 MB │
 public │ i_tc_lud_agi            │ index │ pg    │ tc    │ 30 MB │
 public │ i_tc_uniqueness_hash_v2 │ index │ pg    │ tc    │ 21 MB │
(7 rows)

Note that i_tc_lud_agi is 30 MB, not 57MB, and that i_tc_adid_tid is
36 MB, not 57 MB.

I can see that both i_tc_lud_agi and i_tc_adid_tid consistently use
the "split after new tuple" optimization on v12.

-- 
Peter Geoghegan