Postgresql Abfragezeit

Communities Deutschland (Germany)
1

Hallo,

ich habe die Postgresqlfunktion zum testen von Key Value Paaren etwas erweitert:

CREATE OR REPLACE FUNCTION c_v(planet_osm_ways.tags%TYPE, text[][])
  RETURNS boolean AS'
    DECLARE

    BEGIN
        if array_upper($1,1)<2 THEN
            Return false;
        END IF;
        FOR i IN 1..(array_upper($1,1)) LOOP
            
                if ($1[i]=$2[1][1] and $1[i+1]=$2[1][2]) Then
                    Return true;
                END IF;
                i:=i+1;
                
            
        END LOOP;
        Return false;
    END;
' LANGUAGE plpgsql

Außerdem gibt es noch eine Funktion

 CREATE OR REPLACE FUNCTION c_k_v(planet_osm_ways.tags%TYPE, text[][])
  RETURNS boolean AS'
    DECLARE

    BEGIN
        if array_upper($1,1)<2 THEN
            Return false;
        END IF;
        FOR i IN 1..(array_upper($1,1)) LOOP
            FOR j IN 1..(array_upper($2,1)) LOOP
                if ($1[i]=$2[j][1] and $1[i+1]=$2[j][2]) Then
                    Return true;
                END IF;
                i:=i+1;
                j:=j+1;
            END LOOP;
        END LOOP;
        Return false;
    END;
' LANGUAGE plpgsql

Beide Funktionen arbeiten einwandfrei. Allerdings verstehe ich nicht warum es so große zeitliche Unterschiede beim Ausführen der beiden Funktionen gibt.
Die Abfragen

select * FROM planet_osm_rels where c_v(tags,'{{"route","tram"}}') OR c_v(tags,'{{"route","bus"}}');
select * FROM planet_osm_rels where c_k_v(tags,'{{"route","tram"},{"route","bus"}}');

liefern das gleiche Ergebnis. Allerdings ist die erste Abfrage fast doppelt so schnell und liefert die 439 Zeilen in 1121 ms während die zweite Abfrage 2086 ms benötigt.
Getestet habe ich es mit einer osm2pgsql Datenbank in welches ich das Bundesland Sachsen mit dem Parameter --slim geladen habe.

2

schau dir das doch mal mit "explain verbose select … " an.
Da wird postgresql gesprächig.
Ich kenn das osm2pgsql-Schema fast garnicht. Wenn Indixes auf den Tabellen sind, benutzt er diese nicht unbeding bei OR-Abfragen.
Gruss
walter

3 
"Seq Scan on public.planet_osm_rels  (cost=0.00..6058.42 rows=5857 width=466)"
"  Output: id, way_off, rel_off, parts, members, tags, pending"
"  Filter: (c_v(planet_osm_rels.tags, '{{route,tram}}'::text[]) OR c_v(planet_osm_rels.tags, '{{route,bus}}'::text[]))"

"Seq Scan on public.planet_osm_rels  (cost=0.00..3422.92 rows=3514 width=466)"
"  Output: id, way_off, rel_off, parts, members, tags, pending"
"  Filter: c_k_v(planet_osm_rels.tags, '{{route,tram},{route,bus}}'::text[])"

Ich kann aus diesen beiden Ergebnissen aber noch nicht rauslesen, warum die erste Anfrage mit dem “OR” schneller ist, als die Zweite wobei dort dann auch gleich noch mehr Zeilen zu verarbeiten sind.
Der Aufbau der Tabelle rels

CREATE TABLE planet_osm_rels
(
  id integer NOT NULL,
  way_off smallint,
  rel_off smallint,
  parts integer[],
  members text[],
  tags text[],
  pending boolean NOT NULL,
  CONSTRAINT planet_osm_rels_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE planet_osm_rels
  OWNER TO osm;

-- Index: planet_osm_rels_idx

-- DROP INDEX planet_osm_rels_idx;

CREATE INDEX planet_osm_rels_idx
  ON planet_osm_rels
  USING btree
  (id )
  WHERE pending;

-- Index: planet_osm_rels_parts

-- DROP INDEX planet_osm_rels_parts;

CREATE INDEX planet_osm_rels_parts
  ON planet_osm_rels
  USING gin
  (parts );
4

Mal nur so geraten: Eine Oder-Abfrage prüft den zweiten Teil gar nicht, wenn der erste Teil bereits ein “True” liefert. Deine Zeilen mit Trambahnen kommen bei der ersten Abfrage also alle bereits nach einer Prüfung zurück. Im zweiten Fall werden immer beide Prüfungen (auf Bus und auf Tram) durchgeführt.

5

Das sollte nicht passieren, da die Funktion mit return=true sofort beendet wird.
Also wenn route=tram gefunden wird wird nicht noch nach route=bus gesucht.

6

genau das wollte ich ja wissen: benutzt er bei der schnelleren Abfrage den eventuell vorhandenen Index oder macht er das nicht.
nee, pg macht bei beiden Queries einen seq-scan. das was es also nicht :frowning:
gruss
walter