Date: Fri, 29 May 2015 11:51:17 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-performance@postgresql.org
Subject: Re: Different plan for very similar queries
Message-ID: <20150529095117.GB15813@hjp.at>
References: <20150529085544.GA15813@hjp.at>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="cvVnyQ+4j833TQvp"
Content-Disposition: inline
In-Reply-To: <20150529085544.GA15813@hjp.at>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--cvVnyQ+4j833TQvp
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2015-05-29 10:55:44 +0200, Peter J. Holzer wrote:
> wdsah=3D> explain analyze select facttablename, columnname, term, concept=
_id, t.hidden, language, register=20
>         from term t where facttablename=3D'facttable_stat_fta4' and colum=
nname=3D'einheit' and exists (select 1 from facttable_stat_fta4 f where f.e=
inheit=3Dt.term );
>                                                                          =
      QUERY PLAN                                                           =
                    =20
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------
>  Nested Loop Semi Join  (cost=3D0.00..384860.48 rows=3D1 width=3D81) (act=
ual time=3D0.061..0.119 rows=3D2 loops=3D1)
>    ->  Index Scan using term_facttablename_columnname_idx on term t  (cos=
t=3D0.00..391.46 rows=3D636 width=3D81) (actual time=3D0.028..0.030 rows=3D=
3 loops=3D1)
>          Index Cond: (((facttablename)::text =3D 'facttable_stat_fta4'::t=
ext) AND ((columnname)::text =3D 'einheit'::text))
>    ->  Index Scan using facttable_stat_fta4_einheit_idx on facttable_stat=
_fta4 f  (cost=3D0.00..384457.80 rows=3D21788970 width=3D3) (actual time=3D=
0.027..0.027 rows=3D1 loops=3D3)
>          Index Cond: ((einheit)::text =3D (t.term)::text)
>  Total runtime: 0.173 ms
> (6 rows)
>=20
[...]
> wdsah=3D> explain analyze select facttablename, columnname, term, concept=
_id, t.hidden, language, register=20
>         from term t where facttablename=3D'facttable_stat_fta4' and colum=
nname=3D'berechnungsart' and exists (select 1 from facttable_stat_fta4 f wh=
ere f.berechnungsart=3Dt.term );
>                                                                          =
                QUERY PLAN                                                 =
                                       =20
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------
>  Merge Semi Join  (cost=3D316864.57..319975.79 rows=3D1 width=3D81) (actu=
al time=3D7703.917..30948.271 rows=3D2 loops=3D1)
>    Merge Cond: ((t.term)::text =3D (f.berechnungsart)::text)
>    ->  Index Scan using term_term_idx on term t  (cost=3D0.00..319880.73 =
rows=3D636 width=3D81) (actual time=3D7703.809..7703.938 rows=3D3 loops=3D1)
>          Filter: (((facttablename)::text =3D 'facttable_stat_fta4'::text)=
 AND ((columnname)::text =3D 'berechnungsart'::text))
>    ->  Index Scan using facttable_stat_fta4_berechnungsart_idx on facttab=
le_stat_fta4 f  (cost=3D0.00..2545748.85 rows=3D43577940 width=3D2) (actual=
 time=3D0.089..16263.582 rows=3D21336180 loops=3D1)
>  Total runtime: 30948.648 ms
> (6 rows)

A couple of additional observations:

The total cost of both queries is quite similar, so random variations
might push into one direction or the other. Indeed, after dropping and
recreating indexes (I tried GIN indexes as suggested by Heikki on [1])
and calling analyze after each change, I have now reached a state where
both queries use the fast plan.

In the first case the query planner seems to add the cost of the two
index scans to get the total cost, despite the fact that for a semi join
the second index scan can be aborted after the first hit (so either the
cost of the second scan should be a lot less than 384457.80 or it needs
to be divided by a large factor for the semi join).

In the second case the cost of the second index scan (2545748.85) is
either completely ignored or divided by a large factor: It doesn't seem
to contribute much to the total cost.

	hp


[1] http://hlinnaka.iki.fi/2014/03/28/gin-as-a-substitute-for-bitmap-indexe=
s/

--=20
   _  | Peter J. Holzer    | I want to forget all about both belts and
|_|_) |                    | suspenders; instead, I want to buy pants=20
| |   | hjp@hjp.at         | that actually fit.
__/   | http://www.hjp.at/ |   -- http://noncombatant.org/

--cvVnyQ+4j833TQvp
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)

iQIVAwUBVWg2k/IOSFES/ihdAQKhig//RLD5iTTcPhS7am4PMu2G3+T3ULI/d0Wa
l6i7k7SYU2tVqfVf2YhmhAZfE8dt2y8GE2pL1q9CXbURIjtz6luJhsn3wHC4wpR1
n/amUuv8jxyUiUJliEnR5OVz7JCB40e9O2Sp5CZC4rFbfJCtrQAuCGZEyvKKkTD0
wPR9mF7HE5NUqslQy489oBGDu02UpLuUwPsGI8BYhLvoI8eofqw0oYC4zliDPxVA
iJc6LHQmZsCGGvezm/YFPI9nync5fNW1PRyzvHImjZ0IF5/VVpi4tneS9sl9Au/z
3e8M2hihWIpyV036vqzmDdXx3nBYqbCaIYcZmH5Aiv6fY/5xtPLrOTn0qa5xw7Am
gGQnZ9pJtegzz9NQ+4ZePXnCwAXsIy3MY5zMuxazfeyjyA8jSLTX6Lq1XyOUr4IN
G0C1glOjh/viiKMXTFp9HGycKahrZmWgCxOai4wGKGtpQQLMSNvvjTDdyHigXXE3
WYNyuX5vv01ivdcoRZhy5ndHjvhujMu+6dU2lg0xPJ9eY31YEnbL+4Z7j34XsJtz
6Tq3BKePej+6BPWAdXieM4q5vux0OA3DI8bkjgRAAVaTD0AtXwXTLWG76WYwUd37
A3tn4Uw/I1R8XHE6rodqlkiXM61QmhcQx7Ooaab+WrPBLF1w5HB1saCEmZJ+ZOgF
fS3UZ8Y6ysw=
=+4wj
-----END PGP SIGNATURE-----

--cvVnyQ+4j833TQvp--