AW: AW: CTE with JOIN of two tables is much faster than a regular query

> -----Ursprüngliche Nachricht-----
> Von: Adrian Klaver <adrian.klaver@aklaver.com>
> 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=16loops=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"