> -----Ursprüngliche Nachricht----- > Von: Adrian Klaver <adrian.klaver@xxxxxxxxxxx> > Gesendet: Samstag, 18. August 2018 16:24 > > To try to replicate what the CTE is doing I would try: > SELECT * > FROM Doc > JOIN (SELECT uDocRef, F.oID, Doc.szText > FROM F JOIN Doc ON F.uDocRef = Doc.udocid) AS D > ON D.uDocRef = Doc.udocid > WHERE D.szText ILIKE '%480GB%' No difference - still starting with the full scan on Doc and lasting 67 seconds: "Nested Loop (cost=8006.98..8700.40 rows=5 width=750) (actual time=66845.857..66852.705 rows=10 loops=1)" " -> Hash Join (cost=8006.56..8694.93 rows=5 width=391) (actual time=66845.838..66852.613 rows=10 loops=1)" " Hash Cond: (f.udocref = doc_1.udocid)" " -> Seq Scan on f (cost=0.00..566.05 rows=32605 width=32) (actual time=0.002..3.428 rows=32605 loops=1)" " -> Hash (cost=8006.32..8006.32 rows=19 width=359) (actual time=66845.431..66845.431 rows=16 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 19kB" " -> Seq Scan on doc doc_1 (cost=0.00..8006.32 rows=19 width=359) (actual time=9042.984..66845.398 rows=16 loops=1)" " Filter: (sztext ~~* '%480GB%'::text)" " Rows Removed by Filter: 125930" " -> Index Scan using doc_udocid_key on doc (cost=0.42..1.08 rows=1 width=375) (actual time=0.008..0.008 rows=1 loops=10)" " Index Cond: (udocid = f.udocref)" "Planning time: 252.162 ms" "Execution time: 66852.737 ms"
