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

[Adrian Klaver <adrian.klaver@xxxxxxxxxxx>]

On 08/18/2018 04:08 AM, kpi6288@xxxxxxxxx wrote:
> > -----Ursprüngliche Nachricht-----
> > Von: Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx>
> > Gesendet: Samstag, 18. August 2018 12:27
>   
> > Am 18.08.2018 um 11:36 schrieb kpi6288@xxxxxxxxx:
> > > 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@xxxxxxxxxxx