Re: Same query, same data different plan

On 10/10/22 8:12 AM, Kostas Papadopoulos wrote:
>
> On 10/10/2022 17:53, Tom Lane wrote:
>> Kostas Papadopoulos <kostas(at)methodosit(dot)com(dot)cy> 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.

So there is more to the story.

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?

>>
>> 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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com