Performance Problem

Hallo,
ich habe 2 Datenbanken

DB 1 geonames

CREATE DATABASE geonames
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'German_Germany.1252'
       LC_CTYPE = 'German_Germany.1252'
       CONNECTION LIMIT = -1;

mit folgenden 2 abzufragenen Tabellen:
Tabelle 1 geoname

-- Table: geoname

-- DROP TABLE geoname;

CREATE TABLE geoname
(
  geonameid integer NOT NULL,
  name character varying(200),
  asciiname character varying(200),
  alternatenames text,
  latitude double precision,
  longitude double precision,
  fclass character(1),
  fcode character varying(10),
  country character varying(2),
  cc2 character varying(60),
  admin1 character varying(20),
  admin2 character varying(80),
  admin3 character varying(20),
  admin4 character varying(20),
  population bigint,
  elevation integer,
  dem integer,
  timezone character varying(40),
  moddate date,
  CONSTRAINT pk_geonamid PRIMARY KEY (geonameid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE geoname
  OWNER TO postgres;

-- Index: id_admin1

-- DROP INDEX id_admin1;

CREATE INDEX id_admin1
  ON geoname
  USING btree
  (admin1 COLLATE pg_catalog."default");

-- Index: id_country

-- DROP INDEX id_country;

CREATE INDEX id_country
  ON geoname
  USING btree
  (country COLLATE pg_catalog."default");

-- Index: id_fclass

-- DROP INDEX id_fclass;

CREATE INDEX id_fclass
  ON geoname
  USING btree
  (fclass COLLATE pg_catalog."default");

-- Index: id_fcode

-- DROP INDEX id_fcode;

CREATE INDEX id_fcode
  ON geoname
  USING btree
  (fcode COLLATE pg_catalog."default");

-- Index: id_name

-- DROP INDEX id_name;

CREATE INDEX id_name
  ON geoname
  USING btree
  (name COLLATE pg_catalog."default");

Tabelle 2 alternatename

-- Table: alternatename

-- DROP TABLE alternatename;

CREATE TABLE alternatename
(
  alternatenameid integer NOT NULL,
  geonameid integer,
  isolanguage character varying(7),
  alternatename character varying(200),
  ispreferredname boolean,
  isshortname boolean,
  iscolloquial boolean,
  ishistoric boolean,
  CONSTRAINT an_id PRIMARY KEY (alternatenameid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE alternatename
  OWNER TO postgres;

-- Index: an_islolanguage_id

-- DROP INDEX an_islolanguage_id;

CREATE INDEX an_islolanguage_id
  ON alternatename
  USING btree
  (isolanguage COLLATE pg_catalog."default");

-- Index: an_ispreferred_name

-- DROP INDEX an_ispreferred_name;

CREATE INDEX an_ispreferred_name
  ON alternatename
  USING btree
  (ispreferredname);

-- Index: idx_an_geonameid

-- DROP INDEX idx_an_geonameid;

CREATE INDEX idx_an_geonameid
  ON alternatename
  USING btree
  (geonameid);

DB 2 tourism

CREATE DATABASE tourism
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'German_Germany.1252'
       LC_CTYPE = 'German_Germany.1252'
       CONNECTION LIMIT = -1;

mit 2 foreign_tables

foreign_table geoname

CREATE FOREIGN TABLE geoname (
  geonameid integer NOT NULL,
  name character varying(200),
  asciiname character varying(200),
  alternatenames text,
  latitude double precision,
  longitude double precision,
  fclass character(1),
  fcode character varying(10),
  country character varying(2),
  cc2 character varying(60),
  admin1 character varying(20),
  admin2 character varying(80),
  admin3 character varying(20),
  admin4 character varying(20),
  population bigint,
  elevation integer,
  dem integer,
  timezone character varying(40),
  moddate date
)

foreign_table alternatename

(
  alternatenameid integer NOT NULL,
  geonameid integer,
  isolanguage character varying(7),
  alternatename character varying(200),
  ispreferredname boolean,
  isshortname boolean,
  iscolloquial boolean,
  ishistoric boolean
)

SQL-Abfrage

SELECT
  gn.geonameid AS id,
  CASE
    WHEN
      an.ispreferredname = TRUE
    THEN
      an.alternatename
    ELSE
      gn.name
  END AS name
FROM
  geoname gn
LEFT JOIN alternatename an
  ON 
    an.geonameid = gn.geonameid
  AND
    an.isolanguage = 'de'
  AND
    an.ispreferredname = TRUE
WHERE
  gn.country = 'RU' --Variable country
AND
  gn.fclass = 'A'
AND
  gn.fcode = 'ADM1'
ORDER BY 
  id;

Ergebniss in beiden Datenbanken ist gleich:

id;name
468898;“Yaroslavskaya Oblast’”
472039;“Voronezhskaya Oblast’”
472454;“Vologodskaya Oblast’”
472755;“Volgogradskaya Oblast’”
479119;“Oblast Uljanowsk”
479613;“Udmurtskaya Respublika”
480041;“Tverskaya Oblast’”
480508;“Tul’skaya Oblast’”
484048;“Tatarstan”
484638;“Tambovskaya Oblast’”
487839;“Stavropol’skiy Kray”
491684;“Smolenskaya Oblast’”
498671;“Saratovskaya Oblast’”
499068;“Samarskaya Oblast’”
500059;“Ryazanskaya Oblast’”
501165;“Rostovskaya Oblast’”
504338;“Pskovskaya Oblast’”
511180;“Perm Krai”
511555;“Penzenskaya Oblast’”
514801;“Orlovskaya Oblast’”
515001;“Orenburgskaya Oblast’”
519324;“Novgorodskaya Oblast’”
519969;“North Ossetia”
522652;“Nenetskiy Avtonomnyy Okrug”
524304;“Murmanskaya Oblast’”
524894;“Moskau”
524925;“Moskovskaya Oblast’”
525369;“Respublika Mordoviya”
529352;“Respublika Mariy-El”
535120;“Lipetskaya Oblast’”
536199;“Leningradskaya Oblast’”
536203;“Sankt-Peterburg”
538555;“Kurskaya Oblast’”
542415;“Krasnodarskiy Kray”
543871;“Kostromskaya Oblast’”
545854;“Komi”
548389;“Kirovskaya Oblast’”
552548;“Respublika Kareliya”
552927;“Karachayevo-Cherkesiya”
553899;“Kaluzhskaya Oblast’”
553972;“Kalmykiya”
554230;“Kaliningradskaya Oblast’”
554667;“Kabardino-Balkarskaya Respublika”
555235;“Ivanovskaya Oblast’”
556349;“Respublika Ingushetiya”
559838;“Nizhegorodskaya Oblast’”
567293;“Dagestan”
567395;“Chuvashskaya Respublika”
569665;“Chechenskaya Respublika”
571473;“Bryanskaya Oblast’”
578071;“Belgorodskaya Oblast’”
578853;“Bashkortostan”
580491;“Astrakhanskaya Oblast’”
581043;“Arkhangel’skaya Oblast’”
584222;“Respublika Adygeya”
826294;“Vladimirskaya Oblast’”
1486462;“Yamalo-Nenetskiy Avtonomnyy Okrug”
1488747;“Tyumenskaya Oblast’”
1488873;“Respublika Tyva”
1489421;“Tomskaya Oblast’”
1490542;“Sverdlovskaya Oblast’”
1496152;“Omskaya Oblast’”
1496745;“Novosibirskaya Oblast’”
1501312;“Kurganskaya Oblast’”
1502020;“Krasnoyarskiy Kray”
1503773;“Khanty-Mansiyskiy Avtonomnyy Okrug - Yugra”
1503834;“Respublika Khakasiya”
1503900;“Kemerovskaya Oblast’”
1506272;“Respublika Altay”
1508290;“Chelyabinskaya Oblast’”
1511732;“Altayskiy Kray”
2013162;“Respublika Sakha (Yakutiya)”
2017623;“Primorskiy Kray”
2022888;“Khabarovskiy Kray”
2023468;“Irkutskaya Oblast’”
2026639;“Yevreyskaya Avtonomnaya Oblast’”
2027748;“Amurskaya Oblast’”
2050915;“Respublika Buryatiya”
2121529;“Sakhalinskaya Oblast’”
2123627;“Magadanskaya Oblast’”
2125072;“Kamtchatski Kray”
2126099;“Chukotskiy Avtonomnyy Okrug”
7779061;“Zabaykal’skiy Kray”

Bei Abfrage in der DB 1 geonames dauert die Abfrage 3275 ms

Bei Abfrage in DB 2 tourism dauert die Abfrage 101489 ms

  1. Warum dauern die Abfragen so lange?
  2. Warum ist der Unterschied zwischen DB 1 und DB 2 so gravierend?

DB2 verwendet foreign tables. IIRC werden (derzeit noch) Where-Conditions NICHT an die fremde DB weitergereicht. Frage beantwortet?

mmmm, teilweise…

Also:

  1. Warum dauert die Abfrage in der DB 1 so lange? Welche Verbesserungen kann ich hier einbauen?
  2. Welche Möglichkeiten gibt es die Performance in der DB 2 zu verbessern?
  3. Was ist IIRC?

→ Explain befragen

  1. Welche Möglichkeiten gibt es die Performance in der DB 2 zu verbessern?

PostgreSQL: Documentation: 9.3: postgres_fdw, F.31.4. Remote Query Optimization

  1. Was ist IIRC?

https://www.google.de/?gws_rd=ssl#q=iirc



EXPLAIN
SELECT
...

Ergebniss:

QUERY PLAIN
“Sort (cost=7764.95…7764.96 rows=5 width=35)”
" Sort Key: gn.geonameid"
" → Nested Loop Left Join (cost=7148.19…7764.89 rows=5 width=35)"
" → Bitmap Heap Scan on geoname gn (cost=7147.75…7721.77 rows=5 width=18)"
" Recheck Cond: (((fcode)::text = ‘ADM1’::text) AND (fclass = ‘A’::bpchar))"
" Filter: ((country)::text = ‘RU’::text)"
" → BitmapAnd (cost=7147.75…7147.75 rows=146 width=0)"
" → Bitmap Index Scan on id_fcode (cost=0.00…90.63 rows=4026 width=0)"
" Index Cond: ((fcode)::text = ‘ADM1’::text)"
" → Bitmap Index Scan on id_fclass (cost=0.00…7056.87 rows=328591 width=0)"
" Index Cond: (fclass = ‘A’::bpchar)"
" → Index Scan using idx_an_geonameid on alternatename an (cost=0.44…8.62 rows=1 width=21)"
" Index Cond: (geonameid = gn.geonameid)"
" Filter: (ispreferredname AND ((isolanguage)::text = ‘de’::text))"

Ok, so weit so gut…was fang ich jetzt damit an?
Ich nehme an, ich muss mich auf cost konzentrieren?
cost (Übersetzung):

Angaben über den geschätzten Inbetriebnahme und Gesamtkosten der einzelnen Planknoten, sowie die geschätzte Anzahl der Zeilen und die geschätzte Breite von jeder Zeile. Dieser Parameter ist standardmäßig auf TRUE.

Aber was konkret kann ich daraus lesen in Bezug auf Verbesserung?

Beispiel


→ Bitmap Index Scan on id_fclass (cost=0.00…7056.87 rows=328591 width=0)

Mach mal explain analyse , das führt das auch aus. Aber an sich sieht das nicht schlecht aus, die Indexe werden ja genutzt.



Query Plan
“Sort (cost=7764.95…7764.96 rows=5 width=35) (actual time=29308.660…29308.675 rows=83 loops=1)”
" Sort Key: gn.geonameid"
" Sort Method: quicksort Memory: 22kB"
" → Nested Loop Left Join (cost=7148.19…7764.89 rows=5 width=35) (actual time=21559.399…29307.685 rows=83 loops=1)"
" → Bitmap Heap Scan on geoname gn (cost=7147.75…7721.77 rows=5 width=18) (actual time=21541.252…27174.581 rows=83 loops=1)"
" Recheck Cond: (((fcode)::text = ‘ADM1’::text) AND (fclass = ‘A’::bpchar))"
" Filter: ((country)::text = ‘RU’::text)"
" Rows Removed by Filter: 3815"
" → BitmapAnd (cost=7147.75…7147.75 rows=146 width=0) (actual time=8918.934…8918.934 rows=0 loops=1)"
" → Bitmap Index Scan on id_fcode (cost=0.00…90.63 rows=4026 width=0) (actual time=3.074…3.074 rows=3898 loops=1)"
" Index Cond: ((fcode)::text = ‘ADM1’::text)"
" → Bitmap Index Scan on id_fclass (cost=0.00…7056.87 rows=328591 width=0) (actual time=8914.048…8914.048 rows=315305 loops=1)"
" Index Cond: (fclass = ‘A’::bpchar)"
" → Index Scan using idx_an_geonameid on alternatename an (cost=0.44…8.62 rows=1 width=21) (actual time=25.664…25.666 rows=0 loops=83)"
" Index Cond: (geonameid = gn.geonameid)"
" Filter: (ispreferredname AND ((isolanguage)::text = ‘de’::text))"
" Rows Removed by Filter: 15"
“Total runtime: 29311.230 ms”

Ich lasse die Abfragen via PgAmin III machen, was mich wundert, wenn ich dieselben Abfragen in ein php-Script (PDO) einfüge und ausführe, gehen sie viel schneller

der Plan und die tatsächlichen Rows und so sehen passend aus. Dauert nur das explain analyse länger, oder auch die Abfrage selber in pgadmin?
Ich nutze pgadmin nicht, da kann ich eher wenig helfen. Aber das Messen der Zeiten kann auf manchen Hardwaren zu einer erheblichen Performancebremse werden, vielleicht spielt das hier rein.