On 08/18/2018 04:08 AM, kpi6...@gmail.com wrote:


-----Ursprüngliche Nachricht-----
Von: Andreas Kretschmer <andr...@a-kretschmer.de>
Gesendet: Samstag, 18. August 2018 12:27
Am 18.08.2018 um 11:36 schrieb kpi6...@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;

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%'




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"





--
Adrian Klaver
adrian.kla...@aklaver.com

Reply via email to