Re: "order by" and "order by asc" returning different results on date field

Table definition:
create table tbl_recognition_logging(
created timestamp default current_timestamp,
account_id int not null,
group_id int not null,
action text not null,
environment text not null,
result text not null
);

Without 'asc':
[
{
"Execution Time": 97.782,
"Planning Time": 0.057,
"Plan": {
"Temp Written Blocks": 950,
"Sort Key": [
"tbl_recognition_logging.created"
],
"Node Type": "Sort",
"Sort Space Used": 7600,
"Actual Total Time": 84.425,
"Shared Hit Blocks": 1171,
"Plans": [
{
"Node Type": "Seq Scan",
"Actual Total Time": 20.459,
"Shared Hit Blocks": 1171,
"Schema": "public",
"Shared Read Blocks": 0,
"Relation Name": "tbl_recognition_logging",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 118,
"Total Cost": 1958.04,
"Actual Startup Time": 0.014,
"Filter": "(tbl_recognition_logging.action = 'identify'::text)",
"Alias": "tbl_recognition_logging",
"Temp Read Blocks": 0,
"Output": [
"created",
"account_id",
"group_id",
"action",
"environment",
"result"
],
"Local Read Blocks": 0,
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 60031,
"Parallel Aware": false,
"Actual Rows": 59720,
"Parent Relationship": "Outer",
"Actual Loops": 1,
"Rows Removed by Filter": 2849
}
],
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Sort Method": "external sort",
"Plan Width": 118,
"Actual Loops": 1,
"Actual Startup Time": 67.139,
"Temp Read Blocks": 950,
"Output": [
"created",
"account_id",
"group_id",
"action",
"environment",
"result"
],
"Local Read Blocks": 0,
"Shared Written Blocks": 0,
"Startup Cost": 10418.52,
"Shared Dirtied Blocks": 0,
"Sort Space Type": "Disk",
"Local Written Blocks": 0,
"Plan Rows": 60031,
"Parallel Aware": false,
"Actual Rows": 59720,
"Total Cost": 10568.6
},
"Triggers": []
}
]

With asc:
[
{
"Execution Time": 97.447,
"Planning Time": 0.053,
"Plan": {
"Temp Written Blocks": 950,
"Sort Key": [
"tbl_recognition_logging.created"
],
"Node Type": "Sort",
"Sort Space Used": 7600,
"Actual Total Time": 84.076,
"Shared Hit Blocks": 1171,
"Plans": [
{
"Node Type": "Seq Scan",
"Actual Total Time": 20.507,
"Shared Hit Blocks": 1171,
"Schema": "public",
"Shared Read Blocks": 0,
"Relation Name": "tbl_recognition_logging",
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Temp Written Blocks": 0,
"Plan Width": 118,
"Total Cost": 1958.04,
"Actual Startup Time": 0.012,
"Filter": "(tbl_recognition_logging.action = 'identify'::text)",
"Alias": "tbl_recognition_logging",
"Temp Read Blocks": 0,
"Output": [
"created",
"account_id",
"group_id",
"action",
"environment",
"result"
],
"Local Read Blocks": 0,
"Startup Cost": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Written Blocks": 0,
"Plan Rows": 60031,
"Parallel Aware": false,
"Actual Rows": 59720,
"Parent Relationship": "Outer",
"Actual Loops": 1,
"Rows Removed by Filter": 2849
}
],
"Shared Read Blocks": 0,
"Local Hit Blocks": 0,
"Local Dirtied Blocks": 0,
"Sort Method": "external sort",
"Plan Width": 118,
"Actual Loops": 1,
"Actual Startup Time": 66.835,
"Temp Read Blocks": 950,
"Output": [
"created",
"account_id",
"group_id",
"action",
"environment",
"result"
],
"Local Read Blocks": 0,
"Shared Written Blocks": 0,
"Startup Cost": 10418.52,
"Shared Dirtied Blocks": 0,
"Sort Space Type": "Disk",
"Local Written Blocks": 0,
"Plan Rows": 60031,
"Parallel Aware": false,
"Actual Rows": 59720,
"Total Cost": 10568.6
},
"Triggers": []
}
]

Creating a "self contained test case" is out of scope for me considering my
current workload, but I'm happy to answer any questions. Additionally, I
just ran "select * from tbl_recognition_logging order by created;" again
and found that the results are not properly ordered.

Regards,
Rick Gentry
-----------------
Zenus, Inc.

On Tue, May 15, 2018 at 3:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Rick Gentry <rick(dot)gentry(at)zenus-biometrics(dot)com> writes:
> > PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
> > 20140911 (Red Hat 4.8.3-9), 64-bit
>
> Hm. You might try updating to current (9.6.9), though this doesn't
> sound much like any recent bug fix.
>
> > The result set looks like some form of merge sort was being performed,
> but
> > the system decided to skip the last few merge iterations.
> >
> > You're correct, the statement I've sent does not reproduce the issue.
> The
> > actual query I'm using is much longer, but I've managed to reproduce the
> > issue with "select * from tbl_recognition_logging where action='identify'
> > order by created". 'action' is a text field and I see that pgAdmin 4
> > highlights it like a reserved word.
>
> Can we see EXPLAIN ANALYZE output for both the working and non-working
> cases?
>
> If the system is relying on an indexscan to produce the sort ordering,
> it's possible that this traces to some kind of index corruption. But
> that's purely a guess.
>
> On the whole, I think we're going to need a self-contained test case
> to get far with this.
>
> regards, tom lane
>