Alex Adriaanse <alex@innovacomputing.com> writes:
> Its row estimates are still way off. As a matter of fact, it almost seems as
> if the index doesn't affect row estimates at all.
Indexes normally don't affect estimates. Expression indexes do effectively
create a new column to generate stats for, but that doesn't really help here
because there aren't any estimation functions for the geometric gist indexes.
> -> BitmapAnd (cost=8.99..8.99 rows=1 width=0) (actual time=0.485..0.485 rows=0 loops=135)
> -> Bitmap Index Scan on test_table_2_s_id (cost=0.00..2.17 rows=48 width=0) (actual time=0.015..0.015 rows=1
loops=135)
> Index Cond: (s_id = 13300613::numeric)
> -> Bitmap Index Scan on test_table_2_n_id (cost=0.00..6.57 rows=735 width=0) (actual time=0.467..0.467
rows=815loops=135)
> Index Cond: ("outer".id = test_table_2.n_id)
If this query is representative then it seems you might be better off without
the test_table_2_n_id index. Of course this could be a problem if you need
that index for other purposes.
I'm puzzled how test_table_2_s_id's estimate isn't more precise. Are there
some values of s_id that are quite common and others that are unique? You
might try raising the statistics target on s_id.
Incidentally, 70ms is pretty good. I'm usually happy if all my mundane queries
are under 100ms and the more complex queries in the vicinity of 300ms. Trying
to optimize below 100ms is hard because you'll find a lot of variability in
the performance. Any extraneous disk i/o from checkpoints, vacuums, even other
services, will throw off your expectations.
--
greg