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
- Warum dauern die Abfragen so lange?
- Warum ist der Unterschied zwischen DB 1 und DB 2 so gravierend?