Re: AW: CTE with JOIN of two tables is much faster than a regularquery

On 08/18/2018 04:08 AM, kpi6288@gmail.com wrote:
> 
> 
>> -----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;

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.klaver@aklaver.com