Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YyG58-0008TQ-LT for pgsql-performance@arkaria.postgresql.org; Fri, 29 May 2015 08:55:58 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YyG58-0000bX-4e for pgsql-performance@arkaria.postgresql.org; Fri, 29 May 2015 08:55:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YyG56-0000ZU-Bv for pgsql-performance@postgresql.org; Fri, 29 May 2015 08:55:56 +0000 Received: from ns1.hjp.at ([212.17.106.132] helo=zeno.hjp.at) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1YyG4y-0007jw-1I for pgsql-performance@postgresql.org; Fri, 29 May 2015 08:55:55 +0000 Received: by zeno.hjp.at (Postfix, from userid 1000) id 35DE7404F; Fri, 29 May 2015 10:55:45 +0200 (CEST) Date: Fri, 29 May 2015 10:55:44 +0200 From: "Peter J. Holzer" To: pgsql-performance@postgresql.org Subject: Different plan for very similar queries Message-ID: <20150529085544.GA15813@hjp.at> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="mP3DRpeJDSE+ciuQ" Content-Disposition: inline User-Agent: Mutt/1.5.21 (2010-09-15) X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --mP3DRpeJDSE+ciuQ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable wdsah=3D> select version(); version = =20 ---------------------------------------------------------------------------= -------------------- PostgreSQL 9.1.15 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7= =2E2-5) 4.7.2, 64-bit (1 row) I plan to upgrade to Debian 8 (with Postgres 9.4) soon, so the problem may go away, but I would still like to understand what is happening here. IRL the queries are a bit more complicated (they involve two additional tables), but I can demonstrate it with just two: wdsah=3D> \d facttable_stat_fta4 Table "public.facttable_stat_fta4" Column | Type | Modifiers=20 ---------------------+-----------------------------+----------- macrobondtimeseries | character varying(255) | not null date | date | not null value | double precision |=20 berechnungsart | character varying |=20 einheit | character varying |=20 kurzbezeichnung | character varying |=20 partnerregion | character varying |=20 og | character varying |=20 sitcr4 | character varying |=20 warenstrom | character varying |=20 valid_from | timestamp without time zone |=20 from_job_queue_id | integer |=20 kommentar | character varying |=20 Indexes: "facttable_stat_fta4_pkey" PRIMARY KEY, btree (macrobondtimeseries, dat= e) "facttable_stat_fta4_berechnungsart_idx" btree (berechnungsart) "facttable_stat_fta4_einheit_idx" btree (einheit) "facttable_stat_fta4_og_idx" btree (og) "facttable_stat_fta4_partnerregion_idx" btree (partnerregion) "facttable_stat_fta4_sitcr4_idx" btree (sitcr4) "facttable_stat_fta4_warenstrom_idx" btree (warenstrom) wdsah=3D> select count(*) from facttable_stat_fta4; count =20 ---------- 43577941 (1 row) wdsah=3D> \d term Table "public.term" Column | Type | Modifiers = =20 ------------------------+-----------------------------+--------------------= ---- facttablename | character varying |=20 columnname | character varying |=20 term | character varying |=20 concept_id | integer | not null language | character varying |=20 register | character varying |=20 hidden | boolean |=20 cleansing_job_queue_id | integer | not null default (-= 1) meta_insert_dt | timestamp without time zone | not null default no= w() meta_update_dt | timestamp without time zone |=20 valid_from | timestamp without time zone |=20 from_job_queue_id | integer |=20 Indexes: "term_concept_id_idx" btree (concept_id) "term_facttablename_columnname_idx" btree (facttablename, columnname) "term_facttablename_idx" btree (facttablename) "term_facttablename_idx1" btree (facttablename) WHERE facttablename IS = NOT NULL AND columnname::text =3D 'macrobondtimeseries'::text "term_language_idx" btree (language) "term_register_idx" btree (register) "term_term_ftidx" gin (to_tsvector('simple'::regconfig, term::text)) "term_term_idx" btree (term) Check constraints: "term_facttablename_needs_columnname_chk" CHECK (facttablename IS NULL = OR columnname IS NOT NULL) Foreign-key constraints: "term_concept_id_fkey" FOREIGN KEY (concept_id) REFERENCES concept(id) = DEFERRABLE wdsah=3D> select count(*) from term; count =20 --------- 6109087 (1 row) The purpose of the query is to find all terms which occur is a given column of the facttable (again, IRL this is a bit more complicated), basically an optimized version of select distinct. Some of my columns have very few distinct members: wdsah=3D> select * from pg_stats where tablename=3D'facttable_stat_fta4' an= d attname in ('einheit', 'berechnungsart', 'warenstrom'); schemaname | tablename | attname | inherited | null_frac = | avg_width | n_distinct | most_common_vals | most_common_freqs | histogr= am_bounds | correlation=20 ------------+---------------------+----------------+-----------+-----------= +-----------+------------+------------------+---------------------+--------= ----------+------------- public | facttable_stat_fta4 | berechnungsart | f | 0 = | 2 | 2 | {n,m} | {0.515167,0.484833} | = | 0.509567 public | facttable_stat_fta4 | einheit | f | 0 = | 3 | 2 | {EUR,kg} | {0.515167,0.484833} | = | 0.491197 public | facttable_stat_fta4 | warenstrom | f | 0 = | 2 | 2 | {X,M} | {0.580267,0.419733} | = | -0.461344 (3 rows) And for some of them my query is indeed very fast: wdsah=3D> explain analyze select facttablename, columnname, term, concept_i= d, t.hidden, language, register=20 from term t where facttablename=3D'facttable_stat_fta4' and columnn= ame=3D'einheit' and exists (select 1 from facttable_stat_fta4 f where f.ein= heit=3Dt.term ); = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------- Nested Loop Semi Join (cost=3D0.00..384860.48 rows=3D1 width=3D81) (actua= l time=3D0.061..0.119 rows=3D2 loops=3D1) -> Index Scan using term_facttablename_columnname_idx on term t (cost= =3D0.00..391.46 rows=3D636 width=3D81) (actual time=3D0.028..0.030 rows=3D3= loops=3D1) Index Cond: (((facttablename)::text =3D 'facttable_stat_fta4'::tex= t) AND ((columnname)::text =3D 'einheit'::text)) -> Index Scan using facttable_stat_fta4_einheit_idx on facttable_stat_f= ta4 f (cost=3D0.00..384457.80 rows=3D21788970 width=3D3) (actual time=3D0.= 027..0.027 rows=3D1 loops=3D3) Index Cond: ((einheit)::text =3D (t.term)::text) Total runtime: 0.173 ms (6 rows) 0.17 ms. Much faster than a plain select distinct over a table with 43 million rows could ever hope to be.=20 warenstrom is very similar and the columns with more distinct values aren't that bad either.=20 But for column berechnungsart the result is bad: wdsah=3D> explain analyze select facttablename, columnname, term, concept_i= d, t.hidden, language, register=20 from term t where facttablename=3D'facttable_stat_fta4' and columnn= ame=3D'berechnungsart' and exists (select 1 from facttable_stat_fta4 f wher= e f.berechnungsart=3Dt.term ); = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------- Merge Semi Join (cost=3D316864.57..319975.79 rows=3D1 width=3D81) (actual= 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 ro= ws=3D636 width=3D81) (actual time=3D7703.809..7703.938 rows=3D3 loops=3D1) Filter: (((facttablename)::text =3D 'facttable_stat_fta4'::text) A= ND ((columnname)::text =3D 'berechnungsart'::text)) -> Index Scan using facttable_stat_fta4_berechnungsart_idx on facttable= _stat_fta4 f (cost=3D0.00..2545748.85 rows=3D43577940 width=3D2) (actual t= ime=3D0.089..16263.582 rows=3D21336180 loops=3D1) Total runtime: 30948.648 ms (6 rows) Over 30 seconds! That's almost 200'000 times slower.=20 The weird thing is that for this particular table einheit and berechnungsart actually have a 1:1 correspondence. Not only is the frequency the same, every row where einheit=3D'kg' has berechnungsart=3D'm' and every row where einheit=3D'EUR' has berechnungsart=3D'n'. So I don't see why two different execution plans are chosen. 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/ --mP3DRpeJDSE+ciuQ Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) iQIVAwUBVWgpj/IOSFES/ihdAQLDMg/9GTPpFdqICX231f/c8an1t3QpI5sBB55/ uWrl542BPLVjWuQhqAUfVGbyjmgTAzkrUoZY47F2SJwCVuxe8j1FttqnVjvomsdo Im6OpqqXfqfN20zqptFKpktF+WsExM77Y1O/KnSbA08ljZeYNW8csa0tZb0GIhIb xH6T9+OlpR3b21TBaDZP8dm7QdKVbpK3qy4V2udjZtx3r5Oc2s4YeaBQg+VfiPPo 8yX6kpflDJ+uG3Y/5Hamska4RSu8bEF0LvkHwBBSDdYsTamAMD2ZYHhIElAqIWNk QmRkO2hoYNCVvaMeQZX9tE/kVfSj8x/E7ZKubqCQE39x2IqyW6x15uJkPr3Gdx4a DnVWSFLIORrKJN3DqoOHq3OYOdNTYlTG6y286Jz1fDgiVp2Qq71K2koH8B7Oh8oW LYVPgn2v0RaTAVYRV/iQ/TeK26wZeO8V8MHeYAhvrTSd8kCgMPe6H0O4IZk0vRxd X9JJKmmQqkhJa27beYIztyyZjn0WwsgXIyLmdZI04tpcyC2YxL3epXzDAVVfso22 2A9lOmLdBFmkmHf+mmYUL5xxaHRSiea2ajv/IuHK+9LyXamlYLAXG9PZ34getg4f H5Ltrfjp+VtKsDpH6Ak4/J2O5qksudInwUehCnPD7GHAo8D8Uxepi/JaH8IFStN2 lcv/fmoPcmc= =nlkm -----END PGP SIGNATURE----- --mP3DRpeJDSE+ciuQ--