Które zapytanie jest najlepsze?

Mam taką tabelę:

CREATE TABLE post (
  user_id INT,
  type_id INT
);

Chciałbym wyciągnąć tych użytkowników, którzy:
- napisali przynajmniej 1 post o typie: 1
- napisali przynajmniej 1 post o typie: 3
- nie napisali żadnego postu o typie innym niż: 1 i 3

Mam kilka propozycji:

Wersja 1)

SELECT DISTINCT user_id
FROM post p1
WHERE EXISTS
(
  SELECT 1
  FROM post p2
  WHERE p1.user_id = p2.user_id
  AND type_id = 1
)
AND EXISTS
(
  SELECT 1
  FROM post p2
  WHERE p1.user_id = p2.user_id
  AND type_id = 3
)
AND NOT EXISTS
(
  SELECT 1
  FROM post p2
  WHERE p1.user_id = p2.user_id
  AND type_id NOT IN (1,3)
)

Wersja 2)

SELECT
  user_id
FROM
  post
GROUP BY
  user_id
HAVING
   COUNT(DISTINCT type_id) = 2
   AND MAX(CASE WHEN type_id IN (1,3) THEN 0 ELSE 1 END) = 0

Wersja 3)

SELECT
  user_id
FROM
  (SELECT DISTINCT user_id, type_id FROM post) p
GROUP BY
  user_id
HAVING
   SUM(CASE WHEN type_id IN (1,3) THEN 1 ELSE 3 END) = 2

Wersja 4)

SELECT user_id FROM post WHERE type_id = 1
INTERSECT
SELECT user_id FROM post WHERE type_id = 3
EXCEPT
SELECT user_id FROM post WHERE type_id NOT IN (1, 3)

Wersja 5)

SELECT
  user_id
FROM
  post
GROUP BY
  user_id
HAVING
   COUNT(DISTINCT type_id) = 2
   AND COUNT(DISTINCT CASE WHEN type_id NOT IN (1, 3) THEN type_id END) = 0

Każda z nich wydaje się działać, ale której powinienem używać? https://dbfiddle.uk/T3WbsQZ6

Nie będę ściemniał, że to moja odpowiedź: zapytałem kolegę GPT.
Odpowiedź jest dość obszerna, tutaj jej podsumowanie.
Ciekawe, czy koledzy dobrze oblatani w bazach potwierdzą to, co wygenerowała AI, czy raczej stwierdzą, że to bzdury?

Rekomendacja
Najlepszy kompromis: Wersja 2
Łączy wydajność z prostotą.

Gdy ważna jest czytelność: Wersja 4
Szczególnie jeśli korzystasz z PostgreSQL, SQL Server lub innych baz obsługujących INTERSECT i EXCEPT.

Do unikania: Wersja 3
Złożona logika SUM może wprowadzać niepotrzebne zamieszanie.

Jeśli używasz np. PostgreSQL, wersja 4 jest świetnym wyborem. W przypadku MySQL, najlepiej użyć wersji 2.

Porównaj plany wykonania tych zapytań, ale niemal na pewno najlepsze będzie wśród tych, które nie agregują danych. Zakładam, że masz założony odpowiedni indeks? W zależności od zapytania indeks będzie na (type_id, user_id) - nie polecam, za mała granularność, albo (user_id, type_id) - ten będzie najlepszy.

@cerrato - uwielbiam odpowiedzi z AI, pokazują tylko wierzchołek góry lodowej (vide dopasowany indeks).

Zasadniczo to żadne z tych zapytań nie spełnia twoich wymagań.

Nie masz tabeli która by mówiła o wszystkich userach więc nie spełnisz warunku " - nie napisali żadnego postu o typie innym niż: 1 i 3"

@cerrato - uwielbiam odpowiedzi z AI, pokazują tylko wierzchołek góry lodowej (vide dopasowany indeks).

@ŁF - wiem, mam tak samo. Dlatego wrzuciłem tutaj to, co GPT wypluło i jestem ciekawy, co powiedzą ludzie w stylu @Marcin.Miga lub inni fachowcy od baz. Ja wiem co to SQL, umiem skleić jakieś zapytania, ale do poziomu eksperta wieeeeeele mi brakuje - stąd moja ciekawość, jak mocny będzie rozjazd pomiędzy AI a realnym doświadczeniem.

To zapytanie przeważanie zwróci 95% użytkowników, czy może zwróci 1%? To zależy od rozkładu danych, więc czasem wariant z agregacją może być lepszym wyborem, niż nested loopy.

select user_id from 
(
  select	/* spodziewany Index Only Scan */ 
	user_id,
	max(case when type_id=1 then 1 else 0 end) has_type1,
	max(case when type_id=2 then 1 else 0 end) has_type2,
	max(case when type_id in (1,2) then 0 else 1 end) has_other_types
  from post
  group by user_id
) where has_type1=1 and has_type2=1 and has_other_types=0;

Index na (user_id,type_id) wydaje się obsługiwać szeroki zakres zapytań. Natomiast w zależności od ilości danych i tego jak często będziesz odpytywał, może warto:

• utworzyć indeks częściowy pod to zapytanie? (user_id,type_id) dla type_id in (1,2,3) - ile masz tych typów i ile % postów jest na danym typie?
• trzymać licznik postów per: user_id, type_id jako osobną relację i na niej operować?
• sprawdzić czy osobne indexy częściowe na type_id=1, type_id=2, type_id not in (2,3) robią różnicę (dla index bitmap scan przy zapytaniach : INTERSECT/EXCEPT)

Najszybsze. Natomiast w zależności od skali i struktury bazy dla różnych zestawów danych różne zapytania dadzą różne wyniki czasowe (przy założeniu że napisałeś je dobrze i zwracają te same wyniki). Pracowałem na bazie gdzie kod z pozoru nielogiczny i bardziej obciążający dawal wyniki szybciej niż legitne podejście także nje ma jednej odpowiedzi

Typowałem, że 5 będzie najszybsza. I dla tych danych tak jest.
https://www.db-fiddle.com/f/6YNvfw8LCZ4Fo7q8yBMrxx/0
Tu pokazuje czasy wykonania.
Dodatkowo "troszkę" ją podrasowałem (wywaliłem DISTINCT z drugiego COUNT, bo jest zupełnie niepotrzebny a zmusza bazę do grupowania) i ... jest jeszcze szybciej.
Przy czasie dorzucę tam więcej danych + indeks i zobaczymy co się zmieni...

Marcin.Miga napisał(a):

Typowałem, że 5 będzie najszybsza. I dla tych danych tak jest.
https://www.db-fiddle.com/f/6YNvfw8LCZ4Fo7q8yBMrxx/0
Tu pokazuje czasy wykonania.
Dodatkowo "troszkę" ją podrasowałem (wywaliłem DISTINCT z drugiego COUNT, bo jest zupełnie niepotrzebny a zmusza bazę do grupowania) i ... jest jeszcze szybciej.
Przy czasie dorzucę tam więcej danych + indeks i zobaczymy co się zmieni...

Wersja piąta zadziała, o ile istnieją tylko trzy typy (1,2,3).

Fac napisał(a):

Wersja piąta zadziała, o ile istnieją tylko trzy typy (1,2,3).

Nie wiem, która jest 5 wg Ciebie, ale gdy dodałem do INSERT dwa rekordy - (1,4), (3,4) to nadal wszystkie zapytania wykonują się poprawnie i zwracają tylko jeden user_id - 5.

Marcin.Miga napisał(a):

Fac napisał(a):

Wersja piąta zadziała, o ile istnieją tylko trzy typy (1,2,3).

Nie wiem, która jest 5 wg Ciebie, ale gdy dodałem do INSERT dwa rekordy - (1,4), (3,4) to nadal wszystkie zapytania wykonują się poprawnie i zwracają tylko jeden user_id - 5.

Racja, mój błąd.

Marcin.Miga napisał(a):

Przy czasie dorzucę tam więcej danych + indeks i zobaczymy co się zmieni...

Jeśli nie dodałeś dopasowanego do zapytania indeksu i nie uruchomiłeś całości na produkcyjnej/przewidywanej ilości danych, to tak naprawdę nic nie sprawdziłeś, bo szybsze może okazać się zupełnie inne zapytanie.