SELECT ... IN (...) Performance

Hallo,

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…?

Vielen Dank,
Christoph

Was sagt Explain dazu? Bitte gleich mit analyse.

Hi,

das ist das Ergebnis von explain analyze:

"Sort  (cost=38238.18..38238.29 rows=41 width=1040) (actual time=303.132..303.135 rows=17 loops=1)"
"  Sort Key: colors.sorting"
"  Sort Method: quicksort  Memory: 26kB"
"  ->  HashAggregate  (cost=38236.57..38237.09 rows=41 width=1040) (actual time=303.092..303.107 rows=17 loops=1)"
"        ->  Hash Join  (cost=1.38..38217.62 rows=1895 width=1040) (actual time=0.164..300.620 rows=1972 loops=1)"
"              Hash Cond: (item_features.color_id = colors.id)"
"              ->  Seq Scan on item_features  (cost=0.00..38189.77 rows=2006 width=4) (actual time=0.124..298.552 rows=1973 loops=1)"
"                    Filter: (((feature)::text ~~ '%arbe:%'::text) AND (item_id = ANY ('{5666,3254,5129,4072,5848,5385,5506,3723,5087,5472,3479,5511,5635,4779,3754,5445,4901,5507,4569,4910,4122,5704,4382,5841,4054,3584,5849,2692,305,4584,5694,4582,5864,4258 (...)"
"                    Rows Removed by Filter: 13162"
"              ->  Hash  (cost=1.17..1.17 rows=17 width=1040) (actual time=0.025..0.025 rows=17 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 2kB"
"                    ->  Seq Scan on colors  (cost=0.00..1.17 rows=17 width=1040) (actual time=0.003..0.011 rows=17 loops=1)"
"Total runtime: 303.282 ms"

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?

Hallo,

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;

Viele Grüße,
Christoph

Hallo,

ich würde mich freuen, wenn sich noch jemand meldet und mir weiter hilft.
Ich habe mehrere derart aufgebaute Queries, die ich optimieren müsste.

VG,
Christoph

Das Problem steht hier: WHERE (feature like ‘%arbe:%’). Das ist nicht via Index machbar.

Doch, ist machbar.

Mit einem Trigram index:

http://www.postgresonline.com/journal/archives/212-PostgreSQL-9.1-Trigrams-teaching-LIKE-and-ILIKE-new-tricks.html

Ah ja :wink: