Re: BitmapAnd on correlated column?

On Thu, 2019-10-03 at 14:22 -0700, greigwise wrote:
> I'm running the following query on Postgres version 10.8:
>
> SELECT count(*) FROM test_table WHERE and id_column_1 IN (9954,
> 9690, 9689, 9688) AND id_column_2 IN ([long list]);
>
> There are 2 indexes, one on id_column_1 and one on id_column_2.
>
> The plan looks like this:
>
> Aggregate
> -> Bitmap Heap Scan on test_table
> -> BitmapAnd
> -> Bitmap Index Scan on index_on_col1
> -> Bitmap Index Scan on index_on_col2
> Planning time: 1.452 ms
> Execution time: 34.036 ms
>
> The thing is that id_column_1 is really dependent on id_column_2. So
> there's really no point in scanning the index on id_column_1. In
> fact, if I remove that in clause for id_column_1 from the query, I
> get a better plan:
>
> Aggregate
> -> Index Only Scan using index_on_col2 on test_table
> Planning time: 0.647 ms
> Execution time: 22.781 ms
>
> I thought maybe extended statistics would help, so I did this:
>
> create statistics test (dependencies) on id_column_2, id_column_1
> from test_table;
> analyze test_table;
>
> But the plan was nearly identical to the first plan with the
> BitmapAND even after creating the extended statistics:
>
> So, I'm just wondering if there's anything I can do to influence the
> optimize to pick the better plan using just the one index on
> id_column_2 (aside from re-writing the query).

Extended statistics will tell PostgreSQL that it is very unlikely
that the first condition will contribute significantly, but that
is no proof that the condition can be omitted, so the optimizer
cannot just skip the condition.

You'll have to rewrite the query.

If one condition depends on the other, consider normalizing the table.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com