> -----Ursprüngliche Nachricht-----
> Von: Andreas Kretschmer <andreas@a-kretschmer.de>
> Gesendet: Samstag, 18. August 2018 12:27
> Am 18.08.2018 um 11:36 schrieb kpi6288@gmail.com:
> > What can I do to improve the performance of the regular query without
> > using a CTE?
>
> try to rewrite it to a subselect:
>
> select ... from ... join (selec ... from ... where ...) x on ...
>
Do mean like this?
EXPLAIN ANALYSE
SELECT F.oID, D.szText
FROM F
JOIN (SELECT Doc.uDocID, Doc.szText FROM Doc WHERE szText ILIKE '%480GB%')
AS D ON D.uDocID = F.uDocRef;
Just as bad as my regular query:
"Hash Join (cost=8006.56..8694.93 rows=5 width=359) (actual
time=66777.898..66784.630 rows=10 loops=1)"
" Hash Cond: (f.udocref = doc.udocid)"
" -> Seq Scan on f (cost=0.00..566.05 rows=32605 width=32) (actual
time=0.002..3.563 rows=32605 loops=1)"
" -> Hash (cost=8006.32..8006.32 rows=19 width=359) (actual
time=66777.471..66777.471 rows=16 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 19kB"
" -> Seq Scan on doc (cost=0.00..8006.32 rows=19 width=359) (actual
time=9013.317..66777.438 rows=16 loops=1)"
" Filter: (sztext ~~* '%480GB%'::text)"
" Rows Removed by Filter: 125930"
"Planning time: 236.354 ms"
"Execution time: 66784.651 ms"