public inbox for [email protected]
help / color / mirror / Atom feedFrom: Merlin Moncure <[email protected]>
To: Flávio Henrique <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: Slow query after 9.3 to 9.6 migration
Date: Thu, 5 Jan 2017 13:01:50 -0600
Message-ID: <CAHyXU0wq7vh+fZmdbh4FEUkDGEAX+kzZWJoQRWhU0M-RY26wug@mail.gmail.com> (raw)
In-Reply-To: <CAOGex3=0DB-R9V558CkeoSOuU4KG_RyNh5etzo85o43xGcuVvQ@mail.gmail.com>
References: <CAOGex3nXTRPZTD-KeoSwD=bj62hQrMK+6h30u09srV71sePqUA@mail.gmail.com>
<CAHyXU0wDi9VjfGC8aQeLsBq4ncLVOKJ=1QR6iRq71U2HXQso4Q@mail.gmail.com>
<CAOGex3=0DB-R9V558CkeoSOuU4KG_RyNh5etzo85o43xGcuVvQ@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique <[email protected]> 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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Slow query after 9.3 to 9.6 migration
In-Reply-To: <CAHyXU0wq7vh+fZmdbh4FEUkDGEAX+kzZWJoQRWhU0M-RY26wug@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox