Subselect als Join?

[pafs72]

Hallo zusammen,

ich tu mich gerade etwas schwer dabei folgendes Subselect als Join darzustellen. Das ganze select ist eigentlich komplexer, aber dieser Teil würde mir schon auf die Sprünge helfen.

Ich habe also folgendes:

    select pfi_1.id
    from
    product_filter_items pfi_1
    where
    pfi_1.categories_id not in (
        select pfi_2.categories_id
        from product_filter_items pfi_2
        where
        pfi_2.id in (139,118) and
        group by pfi_2.categories_id   
    )
    group by pfi_1.id

Ich weiß ich muss die Tabelle mit sich selbst kreuzen, aber ich bekomme die Bedingung nicht hin.
Ich will quasi alle Einträge aus der Tabelle, deren categories_id nicht der categories_id von id 139 und 138 entsprechen.

Danke im Voraus.

VG Frank

 

[akretschmer]

Hallo zusammen,

ich tu mich gerade etwas schwer dabei folgendes Subselect als Join darzustellen. Das ganze select ist eigentlich komplexer, aber dieser Teil würde mir schon auf die Sprünge helfen.

Ich habe also folgendes:


Danke im Voraus.

VG Frank

Was bekommst den für einen Fehler? Einen Syntaxfehler, oder?

Falls Du es nicht hinbekommst, zeig die Tabelle und paar Datensätze, was Du versuchst und was passiert, und was du willst.

test=*# select * from pafs72 ;
 id | cat_id
----+--------
  1 |  100
  2 |  139
  3 |  50
  4 |  118
(4 rows)

Time: 0,161 ms
test=*# select id from pafs72 where cat_id not in (select cat_id from pafs72 where id not in (1,3));
 id
----
  1
  3
(2 rows)

 

[pafs72]

Guten morgen,

... nein, ich bekomme keine Fehlermeldung. Die Abfrage ist nur ziemlich langsam und sie ist eigentlich noch teil einer anderen Abfrage, so dass die ganze Sache zu langsam läuft.

Es handelt sich um eine Tabelle von Produkt-Filter-Einträgen.
Die Filter sind über Kategorien gruppiert.
Wenn ein Filter ausgewählt wird, der schon einen Eintrag mit der gleichen Kategorie hat, sollen keine weiteren Ergebnisse der gleichen Kategorie zurück geliefert werden.

Die Tabelle sieht also so aus:

id | cat_id
137 | 1
138 | 1
139 | 2
140 | 2
141 | 3

Wenn ich id 138 und 139 abfrage, möchte ich als Ergebnis nur noch id 141.

VG Frank

 

[akretschmer]

Guten morgen,

... nein, ich bekomme keine Fehlermeldung. Die Abfrage ist nur ziemlich langsam und sie ist eigentlich noch teil einer anderen Abfrage, so dass die ganze Sache zu langsam läuft.

In dem, was Du gezeigt hast, ist aber ein Syntaxfehler. "and group by". Das ist falsch.

Es handelt sich um eine Tabelle von Produkt-Filter-Einträgen.
Die Filter sind über Kategorien gruppiert.
Wenn ein Filter ausgewählt wird, der schon einen Eintrag mit der gleichen Kategorie hat, sollen keine weiteren Ergebnisse der gleichen Kategorie zurück geliefert werden.

Die Tabelle sieht also so aus:

id | cat_id
137 | 1
138 | 1
139 | 2
140 | 2
141 | 3

Wenn ich id 138 und 139 abfrage, möchte ich als Ergebnis nur noch id 141.

VG Frank

Dann setze passende Indexe und prüfe die Verwendung mit EXPLAIN. Für deine Minitabelle habe ich mal enable_seqscan auf False gesetzt, um zu sehen, ob Indexe genutzt werden können:

test=*# select id from pafs where cat_id in (select cat_id from pafs except (select cat_id from pafs where id in (138, 139)));
id
-----
141
(1 row)

Time: 0,497 ms
test=*# explain select id from pafs where cat_id in (select cat_id from pafs except (select cat_id from pafs where id in (138, 139)));
                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join  (cost=0.27..37.24 rows=5 width=4)
  Join Filter: (pafs.cat_id = "ANY_subquery".cat_id)
  ->  Index Scan using idx_cat_id on pafs  (cost=0.13..12.21 rows=5 width=8)
  ->  Materialize  (cost=0.13..24.67 rows=5 width=4)
        ->  Subquery Scan on "ANY_subquery"  (cost=0.13..24.64 rows=5 width=4)
              ->  HashSetOp Except  (cost=0.13..24.59 rows=5 width=4)
                    ->  Append  (cost=0.13..24.58 rows=7 width=4)
                          ->  Subquery Scan on "*SELECT* 1"  (cost=0.13..12.26 rows=5 width=4)
                                ->  Index Only Scan using idx_cat_id on pafs pafs_1  (cost=0.13..12.21 rows=5 width=4)
                          ->  Subquery Scan on "*SELECT* 2"  (cost=0.13..12.32 rows=2 width=4)
                                ->  Index Scan using idx_id on pafs pafs_2  (cost=0.13..12.30 rows=2 width=4)
                                      Index Cond: (id = ANY ('{138,139}'::integer[]))
(12 rows)

selbes Resultat, andere Abfrage, Indexe werden NICHT alle genutzt:

test=*# select id from pafs where cat_id not in (select cat_id from pafs where id in (138, 139));
id
-----
141
(1 row)

Time: 0,298 ms
test=*# explain select id from pafs where cat_id not in (select cat_id from pafs where id in (138, 139));
                                    QUERY PLAN
-----------------------------------------------------------------------------------
Seq Scan on pafs  (cost=10000000012.31..10000000013.37 rows=2 width=4)
  Filter: (NOT (hashed SubPlan 1))
  SubPlan 1
    ->  Index Scan using idx_id on pafs pafs_1  (cost=0.13..12.30 rows=2 width=4)
          Index Cond: (id = ANY ('{138,139}'::integer[]))
(5 rows)

Kann sein, daß MySQL das EXCEPT nicht kann. MySQL ist halt a bissl behindert...

 

[akretschmer]

Kann sein, daß MySQL das EXCEPT nicht kann. MySQL ist halt a bissl behindert...

test=*# \d pafs
    Table "public.pafs"
Column |  Type  | Modifiers
--------+---------+-----------
id    | integer |
cat_id | integer |
Indexes:
    "idx_cat_id" btree (cat_id)
    "idx_id" btree (id)

test=*# explain select pafs.id from (select cat_id from pafs where id in (138, 139)) foo right join pafs on (foo.cat_id=pafs.cat_id) where foo.cat_id is NULL;
                                    QUERY PLAN
-------------------------------------------------------------------------------------
Merge Anti Join  (cost=0.27..24.46 rows=3 width=4)
  Merge Cond: (pafs.cat_id = pafs_1.cat_id)
  ->  Index Scan using idx_cat_id on pafs  (cost=0.13..12.21 rows=5 width=8)
  ->  Index Scan using idx_cat_id on pafs pafs_1  (cost=0.13..12.22 rows=2 width=4)
        Filter: (id = ANY ('{138,139}'::integer[]))
(5 rows)

Vielleicht geht das ja mit MySQL ...