Date: Sun, 19 Jul 2015 22:41:44 +0200
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-performance@postgresql.org
Subject: Re: Different plan for very similar queries
Message-ID: <20150719204144.GA14234@hjp.at>
References: <20150529085544.GA15813@hjp.at>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="X1bOJ3K7DJ5YkBrT"
Content-Disposition: inline
In-Reply-To: <20150529085544.GA15813@hjp.at>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--X1bOJ3K7DJ5YkBrT
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
> 0.17 ms. Much faster than a plain select distinct over a table with 43
> million rows could ever hope to be.=20
>=20
> warenstrom is very similar and the columns with more distinct values
> aren't that bad either.=20
>=20
> But for column berechnungsart the result is bad:
>=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)
>=20
> Over 30 seconds! That's almost 200'000 times slower.=20

First I'd like to apologize for dropping out of the thread without
providing a test data set. I actually had one prepared (without
confidential data), but I wanted to make sure that I could reproduce the
problem with the test data, and I didn't get around to it for a week or
two and then I went on vacation ...

Anyway, in the meantime you released 9.5alpha (thanks for that, I
probably would have compiled a snapshot sooner or later, but installing
debian packages is just a lot more convenient - I hope you get a lot of
useful feedback) and I installed that this weekend.=20

I am happy to report that the problem appears to be solved. All the
queries of this type I threw at the database finish in a few
milliseconds now.

	hp


--=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/

--X1bOJ3K7DJ5YkBrT
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIVAwUBVawLh/IOSFES/ihdAQL52xAAnLKkbzVkehU6aXEyXxURyWZZf10NGDq5
bUS/bHnc6xMdqa4c4LmUkbneSW5JRpvgGVihn+YZvmU04S97225XMpOv41ZgX6wT
NzlrJ1RlsgRUzSGR9QRnUhk0tePfjA0XhKp9KEu7XFak7uixplCOENrpCZhBXxb2
6bKQJnr0qfhVzgcBFoa9mZzEBMgLTShD+AaJ39UpwN5OybI+1npgcee3dQRMBJ6N
meH3Ju2+ssU9qYBsz9IiuD7X7lSZQSDJ2r4RsDr6Qlz8kzzlPRmVGB8Dvv+vzlfl
/ZcxHfHeOdH+anayaqgeQGcjQp7DEHQ8YPdgIag17pPI65jJnhOEzjHQdXr+6rN0
1bK7o3gjfSXVbeNm2lRHO2zSlak35ho/sNe2dopM3WgqKS3XOG4XF1fddeQrhakE
2Q5lfzqCxwkGH12HPqKjoxQQBO76SdHJn+65RHfMMOf6GdXnSQ5+l2Rgxp4Jgi76
OITtkb9+2aPpey+OHckLhqIEP4ollLIlxknSvPSg4lx5sF+UejdRxCrH9zTQNucc
pmK47bDRHoxJm7L0OsaWEt8YSHizMZCbIJ4RyQpg61/QZwIonYzpBVM/vYy2IIBT
+caRfJw+l5P0jYfGFqX/cd2AuG93y3RVfp5Ucu7yE6DicH00gWu+u0tVZH3fdEj3
d5SuOLiQqgQ=
=H+dQ
-----END PGP SIGNATURE-----

--X1bOJ3K7DJ5YkBrT--