Re: Slow query after 9.3 to 9.6 migration

On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique <yoshimit(at)gmail(dot)com> wrote:
> @Merlin Moncure
>>
>> Big gains (if any) are likely due to indexing strategy.
>> I do see some suspicious casting, for example:
>> Join Filter: ((four_charlie.delta_tango)::integer =
>> (six_quebec.golf_bravo)::integer)
>> Are you casting in the query or joining through dissimilar data types?
>
> No casts in query. The joins are on same data types.

well, something is going on.

create table t(i int);
create table t2(i int);
set enable_hashjoin to false;
set enable_mergejoin to false;

yields:

postgres=# explain select * from t join t2 on t.i = t2.i;
QUERY PLAN
──────────────────────────────────────────────────────────────────
Nested Loop (cost=0.00..97614.88 rows=32512 width=8)
Join Filter: (t.i = t2.i)
-> Seq Scan on t (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t2 (cost=0.00..35.50 rows=2550 width=4)

please note the non-casted join filter.

however,

postgres=# explain select * from t join t2 on t.i::bigint = t2.i::bigint;
QUERY PLAN
──────────────────────────────────────────────────────────────────
Nested Loop (cost=0.00..130127.38 rows=32512 width=8)
Join Filter: ((t.i)::bigint = (t2.i)::bigint)
-> Seq Scan on t (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on t2 (cost=0.00..35.50 rows=2550 width=4)

notice the casts in the join filter. Furthermore, please note the
higher query cost due to the server accounting for the casting
involved in the join. Any kind of non-equality based operation in a
join or the predicate side of a where condition can get very expensive
very quickly. (it remains difficult to see if there's any way to
improve the join operation due to lack of visibility on the query
string).

merlin