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

On 08/18/2018 04:08 AM, kpi6288(at)gmail(dot)com wrote:
>
>
>> -----Ursprüngliche Nachricht-----
>> Von: Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
>> Gesendet: Samstag, 18. August 2018 12:27
>
>> Am 18.08.2018 um 11:36 schrieb kpi6288(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com