ich bin neu in diesem Forum und habe auch gleich eine Frage bzgl. der Performance eines SQL Query.
Es geht im wesentlichen um folgenden Ausdruck:
SELECT … FROM … INNER JOIN … ON … WHERE … AND (item_id IN …) GROUP BY … ORDER BY … asc
Es geht dabei um den Ausdruck (item_id IN …). Die Liste, in der geprüft wird, ob item_id enthalten ist, besteht aus Zahlen (eben IDs). Diese Ids liegen nicht in einem bestimmen Bereich, sie folgen keiner Sortierung etc… sie sind “wild zusammen gewürfelt”.
Die Query braucht so momentan ca. 320 ms. Das ist viel zu lang für uns.
Meine Idee wäre:
ich erstelle zur Laufzeit eine temporäre Tabelle, füge dort die IDs ein und ändere den Query so ab:
SELECT … FROM … INNER JOIN … ON … WHERE … AND (item_id IN SELECT id FROM tmp_ItemIds) GROUP BY … ORDER BY … asc
Ich habe es lokal getestet und die zweite Query braucht ca. 50 ms. Kann jemand diesen Peformance Anstieg begründen. Ich bin mir nämlich nicht sicher, ob mein Test aussagekräftig ist.
Da ich dieses Problem bei einigen ähnlichen Queries habe, würde ich gerne Meinungen dazu einholen.
Wie findet ihr die Idee, habt ihr performantere Vorschläge, Erfahrungen etc…?
Nun siehst Du, wo die Zeit bleibt. Ein Where foo like ‘%arbe:%’ und ähnliche Konstrukte gehen halt eher schlecht zu optimieren. Da müßte man vermutlich am Design der Tabelle ansetzen. Was für Indexe hast Du, wie ist die Tabelle definiert, wie ist das komplette SQL?
der eigentliche Query lautet wie folgt (die Punkte … stehen für weitere Zahlen):
SELECT UPPER(colors.name) as filter_name, colors.code as filter_color FROM “item_features” INNER JOIN “colors” ON “colors”.“id” = “item_features”.“color_id” WHERE (feature like ‘%arbe:%’) AND (item_id (IN 566,3254,…)) GROUP BY color_id, colors.name, colors.code, colors.sorting ORDER BY colors.sorting asc
Vorhandene Indexe:
Primary Key auf item_features.id
Primary Key auf colors.id
Struktur:
CREATE TABLE item_features
(
id serial NOT NULL,
item_id integer DEFAULT 0,
feature character varying(255),
criterion character varying(255),
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
filter_name text,
filter_color text,
color_id integer DEFAULT 0,
CONSTRAINT item_features_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE item_features
OWNER TO fashion;
CREATE TABLE colors
(
id serial NOT NULL,
name character varying(255),
code character varying(255),
published boolean,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
sorting integer DEFAULT 99,
CONSTRAINT colors_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE colors
OWNER TO fashion;