Thread: order by

On 5/11/23 07:29, Marc Millas wrote:
> Hi,
> 
> I keep on investigating on the "death postgres" subject
> but open a new thread as I don't know if it's related to my pb.
> 
> I have 2 different clusters, on 2 different machines, one is prod, the 
> second test.
> Same data volumes.

How can they be sharing the same data 'volume'?

Do you mean you are doing dump/restore between them?

Postgres version for each cluster is?

> 
> On prod if I do
> select col_a, count(col_a) from table_a group by col_a order by col_a desc,
> I get the numbers of NULL on top.
> To get the number of NULL on top on the test db, I have to
> select col_a, count(col_a) from table_a group by col_a order by col_a asc.
> 
> so, it looks like there is something different within the b-tree 
> operator class of varchar (?!?)
> between those 2 clusters.
> 
> What can I check to to explain this difference as, to my understanding, 
> it's not a postgresql.conf parameter.
> 
> thanks
> 
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com <http://www.mokadb.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 5/11/23 08:00, Marc Millas wrote:
> 
> On Thu, May 11, 2023 at 4:43 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 5/11/23 07:29, Marc Millas wrote:
>      > Hi,
>      >
>      > I keep on investigating on the "death postgres" subject
>      > but open a new thread as I don't know if it's related to my pb.
>      >
>      > I have 2 different clusters, on 2 different machines, one is
>     prod, the
>      > second test.
>      > Same data volumes.
> 
>     How can they be sharing the same data 'volume'?
> 
>      roughly: one table is 13080000 lines and the second is 13100000 
> lines, the data comes from yet another DB.
> 
>     those 2 tables have no indexes. they are used to build kind of
>     aggregates thru multiple left joins.
> 
>     Do you mean you are doing dump/restore between them?
> 
> no

So how is the data getting from the third database to the prod and test 
clusters?

For the machines hosting the third db, the prod and test clusters what are?:

OS

OS version

locale


> 
> 
>     Postgres version for each cluster is?
>     14.2

FYI, 14.8 has just been released so the clusters are behind by 6 bug fix 
releases.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 5/11/23 08:29, Marc Millas wrote:
> 
> 

>     So how is the data getting from the third database to the prod and test
>     clusters?
> 
>     For the machines hosting the third db, the prod and test clusters
>     what are?:
> 
> should I understand that you suggest that the way the data is inserted 
> Do change the behaviour of the ORDER BY clause ??

What I am saying is we need context. You are there and know what you are 
looking at and how it got there, we don't. At this point I don't know 
anything as I don't know the data operations involved.

So how did the data get from the third database to the others?

Context is also why the information to the below was requested.

> 
> 
>     OS
> 
>     OS version
> 
>     locale
> 

Without solid information anything said is based on a good deal of 
assuming and we know where that leads.

> 
>      >
>      >
>      >     Postgres version for each cluster is?
>      >     14.2
> 
>     FYI, 14.8 has just been released so the clusters are behind by 6 bug
>     fix
>     releases.
>     Sadly.. I know.
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 5/11/23 09:55, Marc Millas wrote:

Thanks,

I do know about index options.

that table have NO (zero) indexes.

If the table has no indices, then why did you write "it looks like there is something different within the b-tree operator class of varchar"?  After all, you only care about b-trees when you have b-tree indices.

On 5/11/23 09:29, Marc Millas wrote:

Hi,

I keep on investigating on the "death postgres" subject

but open a new thread as I don't know if it's related to my pb.

I have 2 different clusters, on 2 different machines, one is prod, the second test.

Same data volumes.

On prod if I do  

select col_a, count(col_a) from table_a group by col_a order by col_a desc, 

I get the numbers of NULL on top.

To get the number of NULL on top on the test db, I have to 

select col_a, count(col_a) from table_a group by col_a order by col_a asc.

This doesn't answer your question, but: ORDER BY has NULLS { FIRST | LAST } options, so no need to completely change the sort order.

And this just confuses your question:
https://www.postgresql.org/docs/15/sql-select.html

If NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified (thus, the default is to act as though nulls are larger than non-nulls).