Subselect to fajne zapytanie, ale co w sytuacji, kiedy musimy przeszukać miliony rekordów i to setki razy w ciągu minuty. Subselect okaże się mało wydajny, a przy dużej ilości zapytań liczy się każda oszczędność. W portalach społecznościowych często użytkownicy poszukują innych profili na postawie wieku lub wzrostu i tutaj nie ma żadnych problemów, gdyż łatwo przechować je w jednej tabeli. Gorzej jest natomiast z danymi typu znajomość języków obcych czy zainteresowania, gdzie pola te mogą mieć wiele wartości. Niektórzy stosują harcerskie rozwiązania w stylu umieszczania wielu danych do jednego varchar’a i użycie LIKE w zapytaniu. Mnie chyba nie przyszłoby coś takiego do głowy 
Innym pomysłem byłoby zastosowanie pól typu array, lecz odradzane jest to nawet na stronie PostgreSQL:
Tip: Arrays are not sets; searching for specific array elements may be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale up better to large numbers of elements.
Najlepszym rozwiązaniem byłoby zastosowanie kolejnej tabeli z danymi, oddzielnej dla języka i zainteresowań w relacji wiele do wielu z naszą tabelą profili. To dobry sposób zachowujący wszystkie zasady budowania schematów baz danych, niestety trochę powolny. Zapytanie wykonujące subselect na milionach rekordów to wydajnościowa masakra dla bazy danych. W czasach programowania w DOS’ie, kiedy każdy bit i cykl procesora był niezwykle ważny, dane przechowywano jako bity wewnątrz wartości integer, aby zużyć mniejszą ilość pamięci. Taki sam trick można zastosować w bazach danych, by zaoszczędzić miejsce i zasoby podczas przeszukiwania.
Aby pokazać na przykładzie różnice w wydajności zastosowałem poniższy schemat.
Moje dane testowe:
- 11 rekordów w tabelach usr_profile_languages i usr_profile_linterests ( po jedenaście grup)
- ponad milion rekordów w tabeli usr_table
- ponad trzy miliony rekordów w tabelach łączących profil ze znajomościa języków - usr_profile_languages_list, usr_profile_linterests_list (średnio każdy posiada trzy zainteresowania i zna trzy języki)
- indeksy na wieku, kraju, językach, zainteresowaniach
Test z wykorzystaniem subquery:
Aby sprawdzić możliwości bazy rozpocząłem od prostego zapytania:
- select count(*) from usr_table u WHERE age > 56;
Otrzymaliśmy 200 000 rekordów w 1,5 sekundy
- select count(*) from usr_table u WHERE age < 42;
Zapytanie ma ponad 600 000 rekordów i trwało 1,6 sekundy.
Teraz zapytanie o ludzi poniżej 50 lat znających jeden wybrany język z wykorzystaniem podzapytania.
- select count(*) from usr_table u WHERE age < 50 AND (SELECT count(*) FROM usr_profile_languages_list l WHERE l.usr_profile_languages_id=4 AND l.id=u.id)=1;
Zapytanie zabrało od 14s do 50s. Różnica wynika z cachowania zapytania w bazie.
Następnie zapytanie o profile znające dwa języki powyżej 58 roku życia (większość rekordów została wycięta przez index wieku)
- select count(*) from usr_table u WHERE age >58 AND (SELECT count(*) FROM usr_profile_languages_list l WHERE (l.usr_profile_languages_id=4 OR l.usr_profile_languages_id=6) AND l.id=u.id)=2;
Zapytanie wyszukało około 10000 rekordów w ciągu 17 do 40 sekund.
Podobnie jest z ludźmi powyżej 58 lat znającymi dwa z trzech wybranych języków.
- select count(*) from usr_table u WHERE age >58 AND (SELECT count(*) FROM usr_profile_languages_list l WHERE (l.usr_profile_languages_id=4 OR l.usr_profile_languages_id=6 OR l.usr_profile_languages_id=7) AND l.id=u.id)>=2;
18000 rekordów w 14 do 20 sekund.
Test na bitach:
Teraz pola bitowe: tworzymy dane typu integer wewnątrz tabeli użytkowników usr_table i wypełniamy je trzema losowymi zainteresowaniami z jedenastu dostępnych.
alter table usr_table add column languagebit integer;
UPDATE usr_table set languagebit=2^(ROUND(RANDOM()*10+1));
UPDATE usr_table set languagebit=languagebit+2^(ROUND(RANDOM()*10+1));
UPDATE usr_table set languagebit=languagebit+2^(ROUND(RANDOM()*10+1));
Prymitywna skuteczność 
Profile poniżej 50 lat z jednym zainteresowaniem wyszukano w 1,8s .
- select now() ;select count(*) from usr_table u WHERE age <50 AND languagebit & 32 = 32
Użyłem 32 ponieważ 2^5 daje 32, a poszukiwaną grupą była grupa numer 5.
Ludzie powyżej 58 lat z dwoma zainteresowaniami zostali wyszukani w 1,6s
- select count(*) from usr_table u WHERE age >58 AND languagebit & 2 = 2 AND languagebit & 1024 = 1024;
Jak to wszystko działa?
insert danych
wprowadzenie rekordu dla grupy 2 i 3:
dodaj 2^2 + 2^3 = 12 i wprowadzamy do languagebit.
update danych
dodanie kogoś do grupy 5 (2^5=32)
- UPDATE usr_table set languagebit=(languagebit|32) WHERE id=1;
usunięcie osoby z grupy 5
- UPDATE usr_table set languagebit=(languagebit-32) WHERE id=1;
zapytania select:
Wyszukiwanie odpowiedniej grupy.
jeżeli interesuje nas grupa 3. wykonujemy operacje 2^3 = 8
- select count(*) from usr_table u WHERE languagebit & 8 = 8;
dla rekordu w grupie 4 i 5 , mamy 2^4=16 i 2^5=32
- select count(*) from usr_table u WHERE languagebit & (16|32) = 48;
poszukiwanie rekordu zawierającego jedna z dwóch grup 3 lub 6, 2^3=8 i 2^6=64
- select count(*) from usr_table u WHERE languagebit & (8|64) > 0;
Wszystkie testy przeprowadzone były na PostgreSQL 8.0.6 zainstalowanym na FreeBSD 5.5 RELEASE
Konkluzje
Plusy rozwiązania
+ Duży wzrost prędkości zapytania - rozwiązanie z podzapytaniem jest przynajmniej dziesięć razy wolniejsze.
+ Ilość miejsca potrzebna do przechowania tej samej ilości danych jest znacznie mniejsza (brak indeksów, jeden integer zamiast kilku rekordów w tabeli, tylko jedna tabela z danymi)
Minusy rozwiązania
- Brak możliwości sprawdzenia integralności danych, można dodać bit który nie istnieje.
- więcej programowania wymagane jest po stronie aplikacji
Najświeższe komentarze