From: | <kpi6288(at)gmail(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | AW: CTE with JOIN of two tables is much faster than a regular query |
Date: | 2018-08-18 11:08:28 |
Message-ID: | 005301d436e3$cb120960$61361c20$@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----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;
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"
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2018-08-18 14:24:28 | Re: AW: CTE with JOIN of two tables is much faster than a regular query |
Previous Message | Andreas Kretschmer | 2018-08-18 10:26:30 | Re: CTE with JOIN of two tables is much faster than a regular query |