> -----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; 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"
