Same query, same data different plan
I have two identical databases running in the same instance of Postgresql. Ran analyze on both. Running the same query I'm getting different plans, one x10 slower. Although I have solved my problem by re-writing the query, I want to understand why this is happening. If the configuration, Postgresql version, schema and data are the same, what other factors is the planner considering? -- Kostas Papadopoulos KE MethodosIT
Re: Same query, same data different plan
Hi, Yes, I ran ANALYZE in both databases. Kostas On 10/10/2022 16:03, Daevor The Devoted wrote: Hi Is the table stats up to date on both? https://www.postgresql.org/docs/current/planner-stats.html Best regards, Na-iem Dollie On Mon, Oct 10, 2022 at 2:56 PM Kostas Papadopoulos < kos...@methodosit.com.cy> wrote: I have two identical databases running in the same instance of Postgresql. Ran analyze on both. Running the same query I'm getting different plans, one x10 slower. Although I have solved my problem by re-writing the query, I want to understand why this is happening. If the configuration, Postgresql version, schema and data are the same, what other factors is the planner considering? -- Kostas Papadopoulos KE MethodosIT
Re: Same query, same data different plan
Hi, Thank you for responding. My question is not about the performance of a specific query. As I wrote, that is already solved. My question is "how can it be that the same query run in two exactly the same databases can have different plans." Kostas Papadopoulos On 10/10/2022 16:12, Julien Rouhaud wrote: On Mon, Oct 10, 2022 at 04:05:42PM +0300, Kostas Papadopoulos wrote: Hi, Yes, I ran ANALYZE in both databases. Please look at https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide more information.
Re: Same query, same data different plan
Hi, I cannot see how it can be configuration since the two databases are in the same Postgres instance. Kostas Papadopoulos On 10/10/2022 16:16, Pavel Stehule wrote: po 10. 10. 2022 v 15:12 odesÃlatel Julien Rouhaud napsal: On Mon, Oct 10, 2022 at 04:05:42PM +0300, Kostas Papadopoulos wrote: Hi, Yes, I ran ANALYZE in both databases. This can be a common case. Check your configuration: work_mem, shared_buffers, effective_cache_size, random_page_cost, seq_page_cost, ... Please look at https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide more information.
Re: Same query, same data different plan
On 10/10/2022 16:44, Ron wrote: How identical is "identical"? For example, does diff says that "pg_dump --schema-only" of DB1 and DB2 are perfectly identical? And are the table counts identical? I created the second database using pg_dump from the first, so they should be exactly the same. On 10/10/22 08:15, Kostas Papadopoulos wrote: Hi, Thank you for responding. My question is not about the performance of a specific query. As I wrote, that is already solved. My question is "how can it be that the same query run in two exactly the same databases can have different plans." Kostas Papadopoulos On 10/10/2022 16:12, Julien Rouhaud wrote: On Mon, Oct 10, 2022 at 04:05:42PM +0300, Kostas Papadopoulos wrote: Hi, Yes, I ran ANALYZE in both databases. Please look at https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide more information.
Re: Same query, same data different plan
On 10/10/2022 17:53, Tom Lane wrote: Kostas Papadopoulos writes: I cannot see how it can be configuration since the two databases are in the same Postgres instance. There is such a thing as ALTER DATABASE ... SET to install different settings at the per-database level. I understand, but I created the databases to be the same. Our original problem was that developers' workstations (Debian and Windows) were running a specific query different from a test db (Ubuntu). After eliminating everything we thought of (data, versions, configurations, OS, etc) we ended up with the scenario I described here. In general, the answer to your question is that the databases are *not* identical. You just haven't figured out how yet. I'm wondering if it has something to do with the dump/reload having compacted out bloat in the tables or indexes, causing cost estimates to change. I will look into that and a couple of other ideas I got from this list. regards, tom lane Thanks kostas
Re: Same query, same data different plan
Hi Adrian, On 10/10/2022 20:59, Adrian Klaver wrote: Information needed: 1) The query and its EXPLAIN ANALYZE for both slow/fast cases. 2) Postgres version. 3) What database are the developers workstation pointing at? 4) What is the test db and is it the same as 3)? 5) What clients are you using to run the query? Thanks for the interest. I was just looking for pointers on what to look at next. Got that from Tom (table bloat) and Imre (different random sample of analyze). Regards kostas
