Search code examples
postgresqllimit

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

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
Solution

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