(was: CTE with JOIN of two tables is much faster than a regular query)

Am 18.08.18 11:36 schrieb(en) kpi6...@gmail.com: [snip] What can I do to improve the performance of the regular query without using a CTE? Sorry for jumping into this discussion late – I'm facing similar problems with Postgres choosing strange and inefficient query plans for no (for me) appar

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

> -Ursprüngliche Nachricht- > Von: Tim Cross > Gesendet: Sonntag, 19. August 2018 04:57 > > > > We're using object mapping / entity frameworks (e.g. XPO, Entity > Framework Core). These frameworks support regular queries out-of-the > box; a CTEs require additional effort and are more diffi

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

> -Ursprüngliche Nachricht- > Von: Tom Lane > Gesendet: Samstag, 18. August 2018 17:29 > > In any case, given that the ILIKE selects so few rows (and the planner knows > it!), finding a way to index that is clearly the right answer. A trigram index took 9 minutes to build but improved th

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

kpi6...@gmail.com writes: >> -Ursprüngliche Nachricht- >> Von: Ravi Krishna >> Gesendet: Samstag, 18. August 2018 18:25 >> >> > What can I do to improve the performance of the regular query without >> using a CTE? >> >> Why do you care ? When I find that I can write a SQL 3 different

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

> -Ursprüngliche Nachricht- > Von: Ravi Krishna > Gesendet: Samstag, 18. August 2018 18:25 > > > What can I do to improve the performance of the regular query without > using a CTE? > > Why do you care ? When I find that I can write a SQL 3 different ways, I will > go for the most effic

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

> What can I do to improve the performance of the regular query without using a > CTE? Why do you care ? When I find that I can write a SQL 3 different ways, I will go for the most efficient one. So why not accept the CTE version of this SQL. Just curious.

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

> -Ursprüngliche Nachricht- > Von: Tom Lane > Gesendet: Samstag, 18. August 2018 17:29 > > Well, it's simpler than that: filter quals are always evaluated at the lowest > possible plan level. Thank you. This "always" was not clear to me, but it explains a few similar cases (with not-so

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

Stephen Frost writes: > * kpi6...@gmail.com (kpi6...@gmail.com) wrote: >> The CTE mentioned below completes the query in 4.5 seconds while the regular >> query takes 66 seconds. > Unfortunately, we don't currently pay attention to things like average > string length when considering the cost of p

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

> -Ursprüngliche Nachricht- > Von: Stephen Frost > Gesendet: Samstag, 18. August 2018 16:39 Hello, > > > What can I do to improve the performance of the regular query without > > using a CTE? > > You could possibly build a trigram index on the field you're searching, which > could avoi

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

Greetings, * kpi6...@gmail.com (kpi6...@gmail.com) wrote: > The CTE mentioned below completes the query in 4.5 seconds while the regular > query takes 66 seconds. I read from EXPLAIN ANALYSE that the regular query > starts with a full table scan over "Doc" while the CTE joins the two tables > firs

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

> -Ursprüngliche Nachricht- > Von: Adrian Klaver > Gesendet: Samstag, 18. August 2018 16:24 > > 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 = D

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

On 08/18/2018 04:08 AM, kpi6...@gmail.com wrote: -Ursprüngliche Nachricht- Von: Andreas Kretschmer Gesendet: Samstag, 18. August 2018 12:27 Am 18.08.2018 um 11:36 schrieb kpi6...@gmail.com: What can I do to improve the performance of the regular query without using a CTE? try t

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

> -Ursprüngliche Nachricht- > Von: Andreas Kretschmer > Gesendet: Samstag, 18. August 2018 12:27 > Am 18.08.2018 um 11:36 schrieb kpi6...@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: > > s

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

Am 18.08.2018 um 11:36 schrieb kpi6...@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 ... Regards, Andreas -- 2ndQuadrant - The PostgreSQL Suppor

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

Running PostgreSQL 9.5 on Windows. The CTE mentioned below completes the query in 4.5 seconds while the regular query takes 66 seconds. I read from EXPLAIN ANALYSE that the regular query starts with a full table scan over "Doc" while the CTE joins the two tables first and applies the filter co