Re: Pg makes nonoptimal choice between two multicolumn indexes with the same columns but in different order.

2013/12/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> I think that's just chance, because AFAICS the cost estimates are exactly
> the same for both indexes, once you've done the vacuum to make all the
> heap pages all-visible. What's more, I'm not sure that that's wrong,
> because according to EXPLAIN (ANALYZE, BUFFERS) the exact same number of
> index pages are touched for either index. So I think Michael's claim that
> the one index is better is at best unproven.
>

Let me prove :)

1. I do benchmarking after dropping Pg and OS disk caches/buffers.
In a way I posted in my first message:
sh# systemctl stop postgresql.service ; echo 3 >/proc/sys/vm/drop_caches ;
systemctl start postgresql.service

And timing results are quite stable: 200-210ms using t1_a_b_idx and
90-100ms using t1_b_a_idx.

Trying 'explain (analyze, buffers) ... ' I got this:
* using t1_a_b_idx:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=46.62..46.63 rows=1 width=0) (actual
time=228.853..228.854 rows=1 loops=1)
Buffers: shared hit=12 read=23
-> Index Only Scan using t1_a_b_idx on t1 (cost=0.57..46.60 rows=7
width=0) (actual time=52.171..228.816 rows=7 loops=1)
Index Cond: ((a = ANY ('{1,9,17,26,35,41,50}'::integer[])) AND (b
= 333333))
Heap Fetches: 7
Buffers: shared hit=12 read=23
Total runtime: 229.012 ms

* using t1_b_a_idx:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=60.12..60.13 rows=1 width=0) (actual
time=115.617..115.617 rows=1 loops=1)
Buffers: shared hit=24 read=11
-> Index Only Scan using t1_b_a_idx on t1 (cost=0.57..60.10 rows=7
width=0) (actual time=80.460..115.590 rows=7 loops=1)
Index Cond: ((b = 333333) AND (a = ANY
('{1,9,17,26,35,41,50}'::integer[])))
Heap Fetches: 7
Buffers: shared hit=24 read=11
Total runtime: 116.480 ms

There is a difference in read operations and moreover in cost estimation.
23 - 11 = 12 excess read operations. If they are all random reads they may
take ~100ms on typical home/workstation SATA hard drive. That's the
difference between
timings I showed above.
Yes, I understand that Pg doesn't know (while planning the query) how many
pages will be hitted in shared buffers.
But I can't get why there is the same buffers count (35) in both plans...
And I can't get why I have different cost estimations...

I grant the theory that the repeated index probes in t1_b_a_idx should be
> more localized than those in t1_a_b_idx, but PG's optimizer doesn't
>

Yes, I see t1_a_b_idx and t1_b_a_idx have 3 levels in btree. For t1_a_b_idx
Pg have to read 1 (header) + 1 (root) + 1 (common level 1 node) + 7 * 2 =
17 pages in it
and for t1_b_a_idx 1 + 1 + 3 = 5 pages ('cause all 7 pairs of (a, b) are
located in one btree leaf node). 17 - 5 = 12 - this is the same difference
as we can see in
'explain (analyze, buffers)'.

> attempt to estimate such effects, and this example isn't doing much to
> convince me that it'd be worth the trouble.
>

In a real life situation I have two kinds of queries for that table:
* select ... from t1 where a in (...) and b = ?
* select ... from t1 where a = ? and b in (...)

I select fields from t1 that are not in indexes thus there is no 'Index
Only Scan', more random reads and performance impact of choosing t1_a_b_idx
in both queries is a bit lesser.

And I got the answer ("PG's optimizer doesn't attempt to estimate such
effects") for my situation.
Thanks a lot.