hey - ich hab die anfrage letztendlich doch ein wenig anders gestaltet:
SELECT DISTINCT on (b.a) b.a, b.b, count(c.e) FROM b, c inner join e using (e) WHERE (b.a = 0 OR b.a = 211070 OR b.a = 2 OR b.a = 509419) AND b.b = e.b GROUP BY b.a, b.b order by b.a, count(c.e) desc;
was ebenfalls funktioniert - aber genauso langsam wie alle anderen Formulierungen.
Ausgeschrieben sieht das dann so aus:
SELECT DISTINCT on (ba.authorid) ba.authorid, ba.bookid, count(userbookid) FROM book_author ba, userbooks ub inner join editions e using (editionid) WHERE (ba.authorid = 0 OR ba.authorid = 211070 OR ba.authorid = 2 OR ba.authorid = 509419) AND ba.bookid = e.bookid GROUP BY ba.authorid, ba.bookid order by ba.authorid,count desc;
und ein EXPLAIN ANASYSE bringt folgendes zu Tage:
Code:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=169307.48..169307.84 rows=73 width=12) (actual time=10163.554..10163.641 rows=2 loops=1)
-> Sort (cost=169307.48..169307.66 rows=73 width=12) (actual time=10163.551..10163.592 rows=65 loops=1)
Sort Key: ba.authorid, (count(ub.userbookid))
Sort Method: quicksort Memory: 28kB
-> HashAggregate (cost=169304.31..169305.22 rows=73 width=12) (actual time=10163.249..10163.312 rows=65 loops=1)
-> Hash Join (cost=12253.51..169288.87 rows=2058 width=12) (actual time=18.367..10160.866 rows=1091 loops=1)
Hash Cond: (ub.editionid = e.editionid)
-> Seq Scan on userbooks ub (cost=0.00..109632.02 rows=6317702 width=8) (actual time=0.017..5134.376 rows=6316408 loops=1)
-> Hash (cost=12205.12..12205.12 rows=3871 width=12) (actual time=11.236..11.236 rows=2095 loops=1)
-> Nested Loop (cost=18.57..12205.12 rows=3871 width=12) (actual time=0.116..9.208 rows=2095 loops=1)
-> Bitmap Heap Scan on book_author ba (cost=18.57..293.04 rows=73 width=8) (actual time=0.088..0.931 rows=139 loops=1)
Recheck Cond: ((authorid = 0) OR (authorid = 211070) OR (authorid = 2) OR (authorid = 509419))
-> BitmapOr (cost=18.57..18.57 rows=73 width=0) (actual time=0.067..0.067 rows=0 loops=1)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (authorid = 0)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.018..0.018 rows=3 loops=1)
Index Cond: (authorid = 211070)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.018..0.018 rows=135 loops=1)
Index Cond: (authorid = 2)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.013..0.013 rows=1 loops=1)
Index Cond: (authorid = 509419)
-> Index Scan using index_bookid on editions e (cost=0.00..162.52 rows=53 width=8) (actual time=0.014..0.037 rows=15 loops=139)
Index Cond: (e.bookid = ba.bookid)
Total runtime: 10163.739 ms
(24 Zeilen) Bei Vermeidung von sequentiellen Scans durch 'SET enable_seqscan TO off;' wird die Abfragezeit jedoch deutlich vermindert:
explain analyse SELECT DISTINCT on (ba.authorid) ba.authorid, ba.bookid, count(userbookid) FROM book_author ba, userbooks ub inner join editions e using (editionid) WHERE (ba.authorid = 0 OR ba.authorid = 211070 OR ba.authorid = 2 OR ba.authorid = 509419) AND ba.bookid = e.bookid GROUP BY ba.authorid, ba.bookid order by ba.authorid,count desc;
Code:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=651614.26..651614.63 rows=73 width=12) (actual time=23.733..23.815 rows=2 loops=1)
-> Sort (cost=651614.26..651614.45 rows=73 width=12) (actual time=23.730..23.769 rows=65 loops=1)
Sort Key: ba.authorid, (count(ub.userbookid))
Sort Method: quicksort Memory: 28kB
-> HashAggregate (cost=651611.09..651612.00 rows=73 width=12) (actual time=23.616..23.662 rows=65 loops=1)
-> Nested Loop (cost=18.57..651595.58 rows=2068 width=12) (actual time=0.164..22.629 rows=1091 loops=1)
-> Nested Loop (cost=18.57..12205.24 rows=3874 width=12) (actual time=0.120..8.117 rows=2095 loops=1)
-> Bitmap Heap Scan on book_author ba (cost=18.57..293.05 rows=73 width=8) (actual time=0.093..0.763 rows=139 loops=1)
Recheck Cond: ((authorid = 0) OR (authorid = 211070) OR (authorid = 2) OR (authorid = 509419))
-> BitmapOr (cost=18.57..18.57 rows=73 width=0) (actual time=0.069..0.069 rows=0 loops=1)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: (authorid = 0)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.013..0.013 rows=3 loops=1)
Index Cond: (authorid = 211070)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.015..0.015 rows=135 loops=1)
Index Cond: (authorid = 2)
-> Bitmap Index Scan on authorid1 (cost=0.00..4.63 rows=18 width=0) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (authorid = 509419)
-> Index Scan using index_bookid on editions e (cost=0.00..162.52 rows=53 width=8) (actual time=0.011..0.031 rows=15 loops=139)
Index Cond: (e.bookid = ba.bookid)
-> Index Scan using index_editionid on userbooks ub (cost=0.00..164.50 rows=44 width=8) (actual time=0.003..0.005 rows=1 loops=2095)
Index Cond: (ub.editionid = e.editionid)
Total runtime: 23.913 ms
(23 Zeilen) Nun meine Frage: Wie kommt sowas? Ist der Planer zu ungenau? Muss ich das nun bei jeder einzelnen Abfrage überprüfen? Oder habe ich generell etwas nicht beachtet bzw. falsch eingestellt?
Lesezeichen