Thread: LIMIT not showing all results
Okay, the subject is a little misleading because of course LIMIT isn't supposed to all results, but I've got an issue whereLIMIT isn't showing the number of results I would expect. For example: mydb=> select id,name,date from analysis where name = 'foo' order by date desc limit 3; id | name | date -----------+-------------+------------------------ 195898786 | foo | 2019-03-05 06:45:29+00 (1 row) mydb=> select id,name,date from analysis where name = 'foo' order by date desc limit 20; id | name | date -----------+-------------+------------------------ 195898786 | foo | 2019-03-05 06:45:29+00 But when I query without limit (or even with "limit all"), there are more: mydb=> select id,name,date from analysis where name = 'foo' order by date desc; id | name | date -----------+-------------+------------------------ 195898786 | foo | 2019-03-05 06:45:29+00 195842671 | foo | 2019-01-24 14:31:45+00 195667475 | foo | 2018-12-30 23:40:11+00 195256709 | foo | 2018-10-29 18:33:07+00 ... (Many more rows) psql version 9.6.11. Server version 9.4.20. We just migrated the database from one server to another (as far as I know, there was no version change), and it worked previously. Any ideas? Many thanks in advance. Casey
Can you run both the queries with
“explain analyze select ....” and paste the output.
On Tue, 5 Mar 2019 at 9:41 PM Casey Deccio <casey@deccio.net> wrote:
Okay, the subject is a little misleading because of course LIMIT isn't supposed to all results, but I've got an issue where LIMIT isn't showing the number of results I would expect. For example:
mydb=> select id,name,date from analysis where name = 'foo' order by date desc limit 3;
id | name | date
-----------+-------------+------------------------
195898786 | foo | 2019-03-05 06:45:29+00
(1 row)
mydb=> select id,name,date from analysis where name = 'foo' order by date desc limit 20;
id | name | date
-----------+-------------+------------------------
195898786 | foo | 2019-03-05 06:45:29+00
But when I query without limit (or even with "limit all"), there are more:
mydb=> select id,name,date from analysis where name = 'foo' order by date desc;
id | name | date
-----------+-------------+------------------------
195898786 | foo | 2019-03-05 06:45:29+00
195842671 | foo | 2019-01-24 14:31:45+00
195667475 | foo | 2018-12-30 23:40:11+00
195256709 | foo | 2018-10-29 18:33:07+00
...
(Many more rows)
psql version 9.6.11.
Server version 9.4.20.
We just migrated the database from one server to another (as far as I know, there was no version change), and it worked previously.
Any ideas?
Many thanks in advance.
Casey
Vijay
> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain <vjain@opentable.com> wrote:
>
> Can you run both the queries with
> “explain analyze select ....” and paste the output.
dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' order by date desc limit 20;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
Limit (cost=0.57..81.74 rows=20 width=31) (actual time=0.540..0.541 rows=1 loops=1)
-> Index Scan Backward using analysis_name_date_key on analysis (cost=0.57..7760.25 rows=1912 width=31) (actual
time=0.539..0.540 rows=1 loops=1)
Index Cond: ((name)::text = 'foo'::text)
Planning time: 6.728 ms
Execution time: 0.587 ms
(5 rows)
dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' order by date desc;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
-----
Sort (cost=7720.96..7725.74 rows=1912 width=31) (actual time=687.953..688.460 rows=4965 loops=1)
Sort Key: date
Sort Method: quicksort Memory: 580kB
-> Bitmap Heap Scan on analysis (cost=75.39..7616.75 rows=1912 width=31) (actual time=8.600..677.916 rows=4965
loops=1)
Recheck Cond: ((name)::text = 'foo'::text)
Heap Blocks: exact=4943
-> Bitmap Index Scan on analysis_name_cache_group_id_key (cost=0.00..74.91 rows=1912 width=0) (actual
time=7.028..7.028rows=4965 loop
s=1)
Index Cond: ((name)::text = 'foo'::text)
Planning time: 0.222 ms
Execution time: 688.897 ms
(10 rows)
Thanks,
Casey
Casey Deccio <casey@deccio.net> writes:
>> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain <vjain@opentable.com> wrote:
>> Can you run both the queries with
>> “explain analyze select ....” and paste the output.
> dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' order by date desc limit 20;
> QUERY PLAN
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> ----------------------------------
> Limit (cost=0.57..81.74 rows=20 width=31) (actual time=0.540..0.541 rows=1 loops=1)
> -> Index Scan Backward using analysis_name_date_key on analysis (cost=0.57..7760.25 rows=1912 width=31) (actual
> time=0.539..0.540 rows=1 loops=1)
> Index Cond: ((name)::text = 'foo'::text)
> Planning time: 6.728 ms
> Execution time: 0.587 ms
> (5 rows)
Hm, so possibly corruption in that index? REINDEX might help.
regards, tom lane
Thanks Tom.
I mean if the instance is a test instance,
probably analysis_name_date_key can be dropped and the query can be
run again so as to check if it still returns the correct rows.
or create an index in parallel with the same col as
analysis_name_date_key and check if the optimizer choses the right
index.
and then come to conclusion of bad index.
Also is there an option where we can force a particular index to be used ?
i read somewhere the below query may help with detecting bad index, is
this correct?
SELECT n.nspname, c.relname
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n,
pg_catalog.pg_index i
WHERE (i.indisvalid = false OR i.indisready = false) AND
i.indexrelid = c.oid AND c.relnamespace = n.oid AND
n.nspname != 'pg_catalog' AND
n.nspname != 'information_schema' AND
n.nspname != 'pg_toast'
Regards,
Vijay
On Tue, Mar 5, 2019 at 10:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Casey Deccio <casey@deccio.net> writes:
> >> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain <vjain@opentable.com> wrote:
> >> Can you run both the queries with
> >> “explain analyze select ....” and paste the output.
>
> > dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' order by date desc limit 20;
> > QUERY PLAN
>
> >
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > ----------------------------------
> > Limit (cost=0.57..81.74 rows=20 width=31) (actual time=0.540..0.541 rows=1 loops=1)
> > -> Index Scan Backward using analysis_name_date_key on analysis (cost=0.57..7760.25 rows=1912 width=31)
(actual
> > time=0.539..0.540 rows=1 loops=1)
> > Index Cond: ((name)::text = 'foo'::text)
> > Planning time: 6.728 ms
> > Execution time: 0.587 ms
> > (5 rows)
>
> Hm, so possibly corruption in that index? REINDEX might help.
>
> regards, tom lane
>
Am 05.03.19 um 17:51 schrieb Vijaykumar Jain: > Thanks Tom. > > > I mean if the instance is a test instance, > probably analysis_name_date_key can be dropped and the query can be > run again so as to check if it still returns the correct rows. > or create an index in parallel with the same col as > analysis_name_date_key and check if the optimizer choses the right > index. > and then come to conclusion of bad index. > > Also is there an option where we can force a particular index to be used ? > no, but you can set enable_indexscan to off and maybe also enable_bitmapscan to off to force the planner to choose a seq-scan. I'm sure in this case you will get a correct result. the other thing is, it would be nice to to know why the index is corrupt. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Andreas Kretschmer <andreas@a-kretschmer.de> writes:
> the other thing is, it would be nice to to know why the index is corrupt.
Given that (a) this was triggered by a server migration and (b)
the leading column of the index looks like it's probably varchar,
I'm suspicious that the new server has different collation behavior.
regards, tom lane
On 5 March 2019 18:54:33 CET, Tom Lane <tgl@sss.pgh.pa.us> wrote: >Andreas Kretschmer <andreas@a-kretschmer.de> writes: >> the other thing is, it would be nice to to know why the index is >corrupt. > >Given that (a) this was triggered by a server migration and (b) >the leading column of the index looks like it's probably varchar, >I'm suspicious that the new server has different collation behavior. > > regards, tom lane yes, sounds reasonable. Regards, Andreas. -- 2ndQuadrant - The PostgreSQL Support Company -- 2ndQuadrant - The PostgreSQL Support Company
On Tue, 5 Mar 2019 at 12:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andreas Kretschmer <andreas@a-kretschmer.de> writes:
> the other thing is, it would be nice to to know why the index is corrupt.
Given that (a) this was triggered by a server migration and (b)
the leading column of the index looks like it's probably varchar,
I'm suspicious that the new server has different collation behavior.
The migration in question was an rsync from a Debian 9 box running 9.4.19-0+deb8u1 to a FreeBSD 11 box running 9.4.20.
On Mar 5, 2019, at 10:37 AM, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:no, but you can set enable_indexscan to off and maybe also enable_bitmapscan to off to force the planner to choose a seq-scan.
I'm sure in this case you will get a correct result.
So this (setting enable_indexscan to off) might be a short-term fix, while reindexing is the more robust fix?
Casey
Am 05.03.19 um 19:09 schrieb Matthew Pounsett: > > > On Tue, 5 Mar 2019 at 12:54, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > Andreas Kretschmer <andreas@a-kretschmer.de > <mailto:andreas@a-kretschmer.de>> writes: > > the other thing is, it would be nice to to know why the index is > corrupt. > > Given that (a) this was triggered by a server migration and (b) > the leading column of the index looks like it's probably varchar, > I'm suspicious that the new server has different collation behavior. > > > The migration in question was an rsync from a Debian 9 box > running 9.4.19-0+deb8u1 to a FreeBSD 11 box running 9.4.20. yeah, check and compare the glibc - version on both systems. (ldd --version) Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Am 05.03.19 um 19:41 schrieb Casey Deccio: > >> On Mar 5, 2019, at 10:37 AM, Andreas Kretschmer >> <andreas@a-kretschmer.de <mailto:andreas@a-kretschmer.de>> wrote: >> >> no, but you can set enable_indexscan to off and maybe also >> enable_bitmapscan to off to force the planner to choose a seq-scan. >> I'm sure in this case you will get a correct result. > > So this (setting enable_indexscan to off) might be a short-term fix, > while reindexing is the more robust fix? > yes, sure. The index is corrupt. Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com
Matthew Pounsett <matt@conundrum.com> writes:
> On Tue, 5 Mar 2019 at 12:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Given that (a) this was triggered by a server migration and (b)
>> the leading column of the index looks like it's probably varchar,
>> I'm suspicious that the new server has different collation behavior.
> The migration in question was an rsync from a Debian 9 box
> running 9.4.19-0+deb8u1 to a FreeBSD 11 box running 9.4.20.
Yeah, that would fit the theory :-(. Debian would be using glibc
and FreeBSD would not be. If you were using C collation in the
database, you'd be all right because that's standardized, but I'll
bet you were using something else. What does psql \l show for the
"collate" setting of this database? (Or, if by chance you had an
explicit COLLATE setting on the column in question, what's that?)
In any case, you should be reindexing any indexes on textual columns
that were not using "C" collation; none of them can be trusted.
The system catalogs should be OK.
regards, tom lane
> On Mar 5, 2019, at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Matthew Pounsett <matt@conundrum.com> writes: >> On Tue, 5 Mar 2019 at 12:54, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Given that (a) this was triggered by a server migration and (b) >>> the leading column of the index looks like it's probably varchar, >>> I'm suspicious that the new server has different collation behavior. > >> The migration in question was an rsync from a Debian 9 box >> running 9.4.19-0+deb8u1 to a FreeBSD 11 box running 9.4.20. > > Yeah, that would fit the theory :-(. Debian would be using glibc > and FreeBSD would not be. If you were using C collation in the > database, you'd be all right because that's standardized, but I'll > bet you were using something else. What does psql \l show for the > "collate" setting of this database? (Or, if by chance you had an > explicit COLLATE setting on the column in question, what's that?) > > In any case, you should be reindexing any indexes on textual columns > that were not using "C" collation; none of them can be trusted. > The system catalogs should be OK. Many thanks to you and everyone who helped with this issue, with informative and actionable advice. Reindexing worked likea champ, and we seem to be back in business. Casey
On Tue, 5 Mar 2019 at 13:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Yeah, that would fit the theory :-(. Debian would be using glibc
and FreeBSD would not be. If you were using C collation in the
database, you'd be all right because that's standardized, but I'll
bet you were using something else. What does psql \l show for the
"collate" setting of this database? (Or, if by chance you had an
explicit COLLATE setting on the column in question, what's that?)
All of the databases are using en_US.UTF-8, which is (I think) the default these days for most distributions, isn't it?
So yeah.. that would be it. Thanks for your help.
The rsync migration was because we needed to do a cross-country copy before putting the original DB server on a truck, but we couldn't get pg_basebackup to complete the 22TB sync without dying. rsync was restartable, so we went that route instead. Now that the two copies are physically next to each other again, after we do a rebuild of the original server I'll be syncing the data back (this time using pg_basebackup and replication). I *assume* we shouldn't expect similar collation problems replicating data that way, but it seems prudent to check.
Should we?
Matthew Pounsett <matt@conundrum.com> writes:
> On Tue, 5 Mar 2019 at 13:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, that would fit the theory :-(. Debian would be using glibc
>> and FreeBSD would not be.
> The rsync migration was because we needed to do a cross-country copy before
> putting the original DB server on a truck, but we couldn't get
> pg_basebackup to complete the 22TB sync without dying. rsync was
> restartable, so we went that route instead. Now that the two copies are
> physically next to each other again, after we do a rebuild of the original
> server I'll be syncing the data back (this time using pg_basebackup and
> replication). I *assume* we shouldn't expect similar collation problems
> replicating data that way, but it seems prudent to check.
> Should we?
Uh, yeah you should. The point here is that Debian and FreeBSD
have different ideas of what en_US.UTF-8 sort order means, so that
an index that's correctly ordered by the lights of one system
is incorrect (corrupt) according to the other.
If you're planninng to install (the same version of) FreeBSD on
the original server hardware, then rsync'ing back from the new
system should be fine. But Debian<->FreeBSD is gonna be trouble
in either direction.
We generally don't recommend physical database transfers across
OS boundaries, and this is the main reason why.
regards, tom lane
On Tue, 5 Mar 2019 at 18:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
If you're planninng to install (the same version of) FreeBSD on
the original server hardware, then rsync'ing back from the new
system should be fine. But Debian<->FreeBSD is gonna be trouble
in either direction.
But I'm specifically NOT talking about doing an rsync to get the data back.. the plan is to use in-protocol replication. Maybe that's a distinction without a difference, but that's why I brought it up.
The replication documentation, and more specifically the pg_basebackup documentation, makes no mention of cross-OS replication as being a problem for any reason. If that is expected to be a problem, then perhaps that should be updated?
Matthew Pounsett <matt@conundrum.com> writes:
> On Tue, 5 Mar 2019 at 18:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If you're planninng to install (the same version of) FreeBSD on
>> the original server hardware, then rsync'ing back from the new
>> system should be fine. But Debian<->FreeBSD is gonna be trouble
>> in either direction.
> But I'm specifically NOT talking about doing an rsync to get the data
> back.. the plan is to use in-protocol replication. Maybe that's a
> distinction without a difference, but that's why I brought it up.
Won't help: all forms of physical replication that we support are just
going to try to copy the indexes bit-for-bit. You could maybe get
away with it if you were using logical replication, but the time penalty
would be severe.
> The replication documentation, and more specifically the pg_basebackup
> documentation, makes no mention of cross-OS replication as being a problem
> for any reason. If that is expected to be a problem, then perhaps that
> should be updated?
Hmm, I'm pretty sure it's documented somewhere, but maybe not in the
places you looked ...
regards, tom lane
On 3/5/19 3:18 PM, Matthew Pounsett wrote: > > > On Tue, 5 Mar 2019 at 18:09, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > > If you're planninng to install (the same version of) FreeBSD on > the original server hardware, then rsync'ing back from the new > system should be fine. But Debian<->FreeBSD is gonna be trouble > in either direction. > > > But I'm specifically NOT talking about doing an rsync to get the data > back.. the plan is to use in-protocol replication. Maybe that's a > distinction without a difference, but that's why I brought it up. > > The replication documentation, and more specifically the pg_basebackup > documentation, makes no mention of cross-OS replication as being a > problem for any reason. If that is expected to be a problem, then > perhaps that should be updated? Generally covered under: https://www.postgresql.org/docs/10/warm-standby.html#STANDBY-PLANNING "It is usually wise to create the primary and standby servers so that they are as similar as possible, at least from the perspective of the database server." You are using binary replication so binary differences come into play. That is why later versions(10+) grew logical replication: https://www.postgresql.org/docs/10/logical-replication.html > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 5 Mar 2019 at 18:39, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> The replication documentation, and more specifically the pg_basebackup
> documentation, makes no mention of cross-OS replication as being a
> problem for any reason. If that is expected to be a problem, then
> perhaps that should be updated?
Generally covered under:
https://www.postgresql.org/docs/10/warm-standby.html#STANDBY-PLANNING
"It is usually wise to create the primary and standby servers so that
they are as similar as possible, at least from the perspective of the
database server."
Nothing in that paragraph says to me that I'm going to have problems as a result of differences in postgres software dependencies. It's a pretty vague warning that seems to imply that as long as your hardware architecture and filesystem layout are identical there shouldn't be any issues.
Thanks for your clarification, though. We'll have to take that into account in our migration plan back to the original server. I don't think we can afford the downtime for a dump/restore between systems, so we'll likely just end up regenerating indexes again, before cutting the application over to the other DB server.
That is why later versions(10+) grew logical replication:
https://www.postgresql.org/docs/10/logical-replication.html
That seems promising, but we'd have to deal with the upgrade to 10.x first. And presumably logical replication would allow more freedom in replicating between mismatched versions as well... possibly as a low-downtime migration path for getting very large databases moved to more recent releases?