Performance degradation with CTEs, switching from PG 11 to PG 15

Hello, I just switched from PG11 to PG15 on our production server (Version is 15.5). Just made a vacuum full analyze on the DB. I have a relatively simple query that used to be fast and is now taking very long (from less than 10 seconds to 3mn+) If I remove a WHERE condition changes the cal

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

John, Le 22/11/2023 à 14:30, John Naylor a écrit : Note that "vacuum full" is not recommended practice in most > situations. Among the downsides, it removes the visibility map, > which is necessary to allow index-only scans. Plain vacuum should > always be used except for certain dire situatio

Re: Performance degradation with CTEs, switching from PG 11 to PG 15

Andreas, Le 22/11/2023 à 15:25, Andreas Kretschmer a écrit : Am 22.11.23 um 12:38 schrieb Jean-Christophe Boggio: >> Also, adding "materialized" to both "withcwrack" and "withcwrack0" >> CTEs gets the result in acceptable timings (a few seconds). The

Strange "actual time" in simple CTE

Hello, I am trying to optimize a complex query and while doing some explains, I stumbled upon this :   CTE cfg     ->  Result  (cost=2.02..2.03 rows=1 width=25) (actual time=7167.478..7167.481 rows=1 loops=1)   Buffers: shared hit=2   InitPlan 1 (returns $0)     ->  L

I don't understand that EXPLAIN PLAN timings

Hello, This EXPLAIN ANALYZE tells me the actual time was 11.xxx ms but the final Execution time says 493.xxx ms (this last one is true : about 1/2 second). I would like to optimize this query but with this inconsistency, it will be difficult. This query is really a function so I added the "p

Re: I don't understand that EXPLAIN PLAN timings

Hello, No answer to my previous email, here is a simpler query with the same problem: explain says actual time between 1.093→1.388 but final execution time says 132.880ms?!? Thanks for your help, explain analyze    WITH RECURSIVE u AS (    SELECT idrealm, canseesubrealm  

Re: I don't understand that EXPLAIN PLAN timings

Hello David, Thanks for your answer. Le 23/01/2024 à 11:41, David Rowley a écrit : If you're using psql, if you do \timing on, how long does EXPLAIN take without ANALYZE? That also goes through executor startup and shutdown. You are absolutely correct : the EXPLAIN without ANALYZE gives about

Re: I don't understand that EXPLAIN PLAN timings

David, It would be good to narrow down which plan node is causing this. Can you try disabling various planner enable_* GUCs before running EXPLAIN (SUMMARY ON) with \timing on and see if you can find which enable_* GUC causes the EXPLAIN to run more quickly? Just watch out for variations in t

Re: I don't understand that EXPLAIN PLAN timings

Hello, In case it might be useful, I made some more tests. On my dev computer (a notebook) I installed: PostgreSQL 15.5 (Ubuntu 15.5-1.pgdg23.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-4ubuntu3) 13.2.0, 64-bit and PostgreSQL 16.1 (Ubuntu 16.1-1.pgdg23.10+1) on x86_64-pc-li

Recursive query slow on strange conditions

Hello, I have a performance/regression problem on a complicated query (placed into a function) when some tables are empty. On Pg 11.6 the query takes 121ms On Pg 12.2 it takes 11450ms I first sent a message to the pgsql-bugs mailing list : https://www.postgresql.org/message-id/16390-e9866af1

Re: Recursive query slow on strange conditions

You can also send a link to the plan on https://explain.depesz.com/ Which maybe more people will look at than if it requires downloading and restoring a DB. Thanks for the advice. Here is the plan for PG 11.6 : https://explain.depesz.com/s/Ewt8 And the one for PG 12.2 : https://explain.depesz.c

Re: Recursive query slow on strange conditions

Hello, I have rewritten the function/query to make it a PLPGSQL function and split the query in ~20 smaller queries. Now the problem of the JIT compiler kicking in also happens on PG 11.6 Although the 2 seconds induced delay is not a serious problem when I execute the query for thousands of i

Re: Recursive query slow on strange conditions

https://www.postgresql.org/docs/12/jit-decision.html Thanks a lot David, I missed that part of the doc. JC

Re: Order of execution

Hello, Le 27/04/2021 à 20:52, luis.robe...@siscobra.com.br a écrit : My question is: is it possible to optimize function order execution? I guess you could change the cost of one of the functions. I personally rewrite my queries but I don't know if it's good practice: WITH pre AS ( SELECT

Re: pg_restore schema dump to schema with different name

The only way to do that is to create a new database, import the data there, rename the schema and dump again. Then import that dump into the target database. Or maybe (if you can afford to have source_schema unavailable for some time) : * rename source_schema to tmp_source * import (that wi

Re: Any way to get nested loop index joins on CTEs?

Following up on this, I very often have to create PLPgSql functions to workaround this problem: create one (or several) temp table(s) (with ON COMMIT DROP), analyze it/them and create indices on some field(s). Being able to write something like: WITH xxx AS MATERIALIZED ANALYZED INDEXED ON fie