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

Why isn't PG using an index-only scan?

Hello, I have this very simple query: INSERT INTO copyrightad (idoeu, idad, role, mechowned, perfowned, iscontrolled) SELECT o.idoeu, c.idad, LEFT(sipa_capacity1,3), sipa_mech_owned, sipa_perf_owned, sipa_controlled='Y' FROM imaestro.msipfl ip JOIN oeu o ON o.imworkid=ip.sipa_song_code JOIN a

Re: Why isn't PG using an index-only scan?

Thanks David, Le 18/09/2025 à 09:20, David Rowley a écrit : Yes. Since *all* records of "oeu" are required and they're not required in any particular order, then Seq Scan should be the fastest way to access those records. Ok but then why is it doing it on the AD table? Is it because of the nu