Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 18:39, Adrian Klaver 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

Re: [External] LIMIT not showing all results

On 3/5/19 3:18 PM, Matthew Pounsett wrote: On Tue, 5 Mar 2019 at 18:09, Tom Lane > 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<-

Re: [External] LIMIT not showing all results

Matthew Pounsett writes: > On Tue, 5 Mar 2019 at 18:09, Tom Lane 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.

Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 18:09, Tom Lane 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 t

Re: [External] LIMIT not showing all results

Matthew Pounsett writes: > On Tue, 5 Mar 2019 at 13:55, Tom Lane 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

Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 13:55, Tom Lane 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

Re: [External] LIMIT not showing all results

> On Mar 5, 2019, at 11:55 AM, Tom Lane wrote: > > Matthew Pounsett writes: >> On Tue, 5 Mar 2019 at 12:54, Tom Lane 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 ne

Re: [External] LIMIT not showing all results

Matthew Pounsett writes: > On Tue, 5 Mar 2019 at 12:54, Tom Lane 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

Re: LIMIT not showing all results

Am 05.03.19 um 19:41 schrieb Casey Deccio: On Mar 5, 2019, at 10:37 AM, Andreas Kretschmer mailto:andr...@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

Re: [External] LIMIT not showing all results

Am 05.03.19 um 19:09 schrieb Matthew Pounsett: On Tue, 5 Mar 2019 at 12:54, Tom Lane > wrote: Andreas Kretschmer mailto:andr...@a-kretschmer.de>> writes: > the other thing is, it would be nice to to know why the index is corrupt. Given that (a) t

Re: LIMIT not showing all results

> On Mar 5, 2019, at 10:37 AM, Andreas Kretschmer > 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) migh

Re: [External] LIMIT not showing all results

On Tue, 5 Mar 2019 at 12:54, Tom Lane wrote: > Andreas Kretschmer 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 su

Re: [External] LIMIT not showing all results

On 5 March 2019 18:54:33 CET, Tom Lane wrote: >Andreas Kretschmer 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 suspic

Re: [External] LIMIT not showing all results

Andreas Kretschmer 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 behav

Re: [External] LIMIT not showing all results

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

Re: [External] LIMIT not showing all results

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 th

Re: [External] LIMIT not showing all results

Casey Deccio writes: >> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain 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; >

Re: [External] LIMIT not showing all results

> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain 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;

Re: [External] LIMIT not showing all results

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 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 numbe

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