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

Re: regex match and special characters

On 08/18/2018 08:12 AM, Oleksii Kliukin wrote: Hi Adrian, On 16. Aug 2018, at 18:13, Adrian Klaver > wrote: test=# select 'abcd'||chr(8198) ~ 'abcd\s'; ?column? -- t (1 row) Wonder if the OP has standard_conforming_strings='off' and escape_string_war

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.

Re: vPgSql

On Sat, 18 Aug 2018 10:52:59 +1000 Tim Cross wrote: > I think pretty much all *nix systems put core shells like sh, bash, zsh > etc in /bin (as it is guaranteed to be available immediately at boot, while > /usr is > not - it could be a separate partition which isn't available until later > in t

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

Re: regex match and special characters

Hi Adrian, > On 16. Aug 2018, at 18:13, Adrian Klaver wrote: > > test=# select 'abcd'||chr(8198) ~ 'abcd\s'; > ?column? > -- > t > (1 row) > > > Wonder if the OP has standard_conforming_strings='off' and > escape_string_warning='off'? > Both are set to ‘on’ for me for all versions (I

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: regex match and special characters

> On 16. Aug 2018, at 16:57, Tom Lane wrote: > > Alex Kliukin writes: >> Here is a simple SQL statement that gives different results on PostgreSQL >> 9.6 and PostgreSQL 10+. The space character at the end of the string is >> actually U+2006 SIX-PER-EM SPACE >> (http://www.fileformat.info/inf

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