Jan Schulz <jasc@gmx.net> writes:
> On 5 December 2017 at 16:10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Can you get a similarly broken plan if you try something involving just
>> this table, say "select * from converting_touchpoints_attribution where
>> touchpoint_fk = 42 and performance_attribution_model_fk = 2" ?
> EXPLAIN ( ANALYSE, BUFFERS )
> select * from m_dim.converting_touchpoints_attribution where
> touchpoint_fk = 42 and performance_attribution_model_fk = 2
> Bitmap Heap Scan on converting_touchpoints_attribution
> (cost=-25769803761.69..-25769803757.67 rows=1 width=16) (actual
> time=0.005..0.005 rows=0 loops=1)
> Recheck Cond: ((performance_attribution_model_fk = 2) AND (touchpoint_fk
> = 42))
> -> BitmapAnd (cost=-25769803761.69..-25769803761.69 rows=1 width=0)
> (actual time=0.004..0.004 rows=0 loops=1)
> -> Bitmap Index Scan on
> converting_touchpoints_attribution__performance_attribution_mod
> (cost=0.00..-12884901880.97 rows=1 width=0) (actual
> time=0.003..0.003 rows=0 loops=1)
> Index Cond: (performance_attribution_model_fk = 2)
> -> Bitmap Index Scan on
> converting_touchpoints_attribution__touchpoint_fk
> (cost=0.00..-12884901880.97 rows=1 width=0) (never executed)
> Index Cond: (touchpoint_fk = 42)
> Planning time: 0.122 ms
> Execution time: 0.036 ms
Hmph. That just raises even more questions --- for instance, why is the
condition (performance_attribution_model_fk = 2) now estimated to select
just 1 row, when previously it was estimated to select 1850 rows?
Still, the fact that you can get a silly answer with just one table
does eliminate some theories I'd been toying with.
The index cost estimation code does have some potential for
garbage-in-garbage-out results, but nothing very promising.
One question is whether these indexes are on a non-default
tablespace with a non-default random_page_cost. PG should
prevent you from putting in a negative random_page_cost, but
it's worth checking that.
I looked at some other possibilities like a corrupted tree_height
value, but none of them seem to fit the available info. For
instance, the tree_height is only an int, so even if it were the
max negative value it would not explain the cost value you're getting.
Don't suppose you'd like to step through btcostestimate() with
a debugger and see where it's going off the rails?
regards, tom lane