Postgres - Same query returns different results when run again and again

Question

While experimenting PostgreSQL for my new project, I have experienced the following query execution behavior. I would like to know why the same query returns different values when run again and again? What is the rational behind this behavior?

phi=# SELECT P.id, p.resource ->> 'birthDate' BD FROM recorditems P WHERE  P.resource @> '{"resourceType":"Patient", "gender":"male"}' AND To_date(P.resource ->> 'birthDate', 'YYYY-MM-DD') > '1975-01-01'::date limit 10;
   id   |     bd
--------+------------
 363661 | 1990-03-08
 363752 | 2006-02-28
 364971 | 2017-10-21
 365330 | 1996-11-25
 367793 | 2007-10-02
 369002 | 2006-09-04
 369172 | 1983-09-10
 369256 | 2001-05-19
 369670 | 1992-03-21
 372082 | 2011-07-27
(10 rows)

Time: 15.085 ms
phi=# SELECT P.id, p.resource ->> 'birthDate' BD FROM recorditems P WHERE  P.resource @> '{"resourceType":"Patient", "gender":"male"}' AND To_date(P.resource ->> 'birthDate', 'YYYY-MM-DD') > '1975-01-01'::date limit 10;
   id   |     bd
--------+------------
 372082 | 2011-07-27
 372645 | 1988-11-02
 373528 | 1984-07-11
 376213 | 1982-01-03
 377386 | 1995-01-20
 377531 | 2002-02-11
 377717 | 1991-11-15
 378372 | 2018-09-27
 378483 | 2009-01-11
 378743 | 1996-02-27
(10 rows)

Time: 18.163 ms 
phi=# SELECT P.id, p.resource ->> 'birthDate' BD FROM recorditems P WHERE  P.resource @> '{"resourceType":"Patient", "gender":"male"}' AND To_date(P.resource ->> 'birthDate', 'YYYY-MM-DD') > '1975-01-01'::date limit 10;
   id   |     bd
--------+------------
 378743 | 1996-02-27
 382517 | 1992-01-14
 387866 | 1985-07-03
 388180 | 1976-11-01
 388627 | 1996-07-10
 396668 | 1979-03-29
 396754 | 2013-05-16
 397054 | 1998-01-05
 401771 | 1983-11-28
 401891 | 2019-03-01
(10 rows)

Time: 44.394 ms

Did you try to sort your query results and to test

adopilot
– adopilot

2020-06-16 09:56:40 +00:00
Commented Jun 16, 2020 at 9:56 — adopilot
– adopilot, Commented Jun 16, 2020 at 9:56

user330315 · Accepted Answer · 2020-06-16 10:36:43Z

2

You are not sorting your results. A LIMIT without an ORDER BY will return (seemingly) random results as the rows of a table are not sorted.

If you want to get consistent results using LIMIT you have to use an ORDER BY as well.

answered Jun 16, 2020 at 10:36

user330315

Sign up to request clarification or add additional context in comments.

Comments

jjanes · Accepted Answer · 2020-06-16 13:54:09Z

0

When you have multiple sequential scans going against the same table at more or less the same time (loosely defined), PostgreSQL tries to synchronize them so that they can all benefit from the same warmed-up cache. If you turn off this feature with set synchronize_seqscans TO off, you will probably get more predictable results. But your original sin here is expecting an order, when you didn't request one.

answered Jun 16, 2020 at 13:54

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

1 Comment

Mahesh V S Over a year ago

First, let me confess for the Sin! :) I was expecting some 'natural' order.

Collectives™ on Stack Overflow

Postgres - Same query returns different results when run again and again

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related