Re: [GENERAL] limit-offset different result sets with same query
2009/5/9 Tom Lane : > Merlin Moncure writes: >> On Fri, May 8, 2009 at 5:40 PM, Alvaro Herrera >> wrote: >>> Yeah, we went over this on the spanish list, turned out that I couldn't >>> remember about syncscan :-) > >> I like the new behavior. It really encourages proper use of order by, >> because the natural ordering results are effectively randomized. A >> class of subtle bugs has been made obvious. :) > > Not really, because the syncscan behavior only kicks in when your table > gets large ... you'll never see it during devel testing on toy tables ... > > regards, tom lane > Yeap. If you see one of the test i made, you'll see this switch over the ~100 regs. -- Emanuel Calvo Franco Sumate al ARPUG ! ( www.arpug.com.ar) ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
On Sun, May 10, 2009 at 2:03 AM, David Fetter wrote: > On Sat, May 09, 2009 at 01:28:03PM -0400, Tom Lane wrote: >> Merlin Moncure writes: >> > On Fri, May 8, 2009 at 5:40 PM, Alvaro Herrera >> > wrote: >> >> Yeah, we went over this on the spanish list, turned out that I >> >> couldn't remember about syncscan :-) >> >> > I like the new behavior. It really encourages proper use of order >> > by, because the natural ordering results are effectively >> > randomized. A class of subtle bugs has been made obvious. :) >> >> Not really, because the syncscan behavior only kicks in when your >> table gets large ... you'll never see it during devel testing on toy >> tables ... > > Good point. It's important not to test only on toy-sized tables for > lots and lots of good reasons, scale-dependence of sync scans being a > small one. Last job I was at I was the lone pgsql guy who worked with three Oracle DBAs, and quite a few of them were caught off guard by this type of behaviour (it was with hash_agg and reporting queries with group by). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
On Sat, May 09, 2009 at 01:28:03PM -0400, Tom Lane wrote: > Merlin Moncure writes: > > On Fri, May 8, 2009 at 5:40 PM, Alvaro Herrera > > wrote: > >> Yeah, we went over this on the spanish list, turned out that I > >> couldn't remember about syncscan :-) > > > I like the new behavior. It really encourages proper use of order > > by, because the natural ordering results are effectively > > randomized. A class of subtle bugs has been made obvious. :) > > Not really, because the syncscan behavior only kicks in when your > table gets large ... you'll never see it during devel testing on toy > tables ... Good point. It's important not to test only on toy-sized tables for lots and lots of good reasons, scale-dependence of sync scans being a small one. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
Merlin Moncure writes: > On Fri, May 8, 2009 at 5:40 PM, Alvaro Herrera > wrote: >> Yeah, we went over this on the spanish list, turned out that I couldn't >> remember about syncscan :-) > I like the new behavior. It really encourages proper use of order by, > because the natural ordering results are effectively randomized. A > class of subtle bugs has been made obvious. :) Not really, because the syncscan behavior only kicks in when your table gets large ... you'll never see it during devel testing on toy tables ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
On Fri, May 8, 2009 at 5:40 PM, Alvaro Herrera wrote: > David Fetter escribió: >> On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: >> > Hi all. >> > >> > I'll make this faster. >> > >> > I hace this table and this function: >> >> You should only ever assume that your SELECT's output will have a >> particular ordering when you include an ORDER BY clause that actually >> specifies the order well enough :) > > Yeah, we went over this on the spanish list, turned out that I couldn't > remember about syncscan :-) I like the new behavior. It really encourages proper use of order by, because the natural ordering results are effectively randomized. A class of subtle bugs has been made obvious. :) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
2009/5/8 David Fetter : > On Fri, May 08, 2009 at 06:40:33PM -0300, Emanuel Calvo Franco wrote: >> >> I test it in the first time :) >> >> With the 'order by' it works well, but in 'theory' > > The theory under which you should operate is that the underlying > implementation only gives you the orderings you ask for. This way, > when other beneficial implementation changes happen, they will not > surprise you. :) Yeap. now it have more sense. Is a question of performance and to take notice of that. Cool. Thanks to all!, -- Emanuel Calvo Franco Sumate al ARPUG ! ( www.arpug.com.ar) ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
On Fri, May 08, 2009 at 06:40:33PM -0300, Emanuel Calvo Franco wrote: > 2009/5/8 David Fetter : > > On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: > >> Hi all. > >> > >> I'll make this faster. > >> > >> I hace this table and this function: > > > > You should only ever assume that your SELECT's output will have a > > particular ordering when you include an ORDER BY clause that > > actually specifies the order well enough :) > > > > I test it in the first time :) > > With the 'order by' it works well, but in 'theory' The theory under which you should operate is that the underlying implementation only gives you the orderings you ask for. This way, when other beneficial implementation changes happen, they will not surprise you. :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
Emanuel Calvo Franco wrote: > > Executing 'select * from datos limit 1 offset 15' two times i have different > result sets. > When i execute 'explain analyze verbose ' i see that (as > expected) the seq scan > is occurring. > > > That's correct? Is logical that if the scan is sequential in the > physical table returns differents > data? > > I test it on 8.4 beta1 and 8.3.5 and 8.3.7 with the same results. > > Thanks in advance. > Emanuel, LIMIT and OFFSET are stable only when you have "ORDER BY" on unique values. Without that, the database is free to return the rows in whatever order it deems best, which gives unpredictable results when combined with LIMIT/OFFSET. Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
David Fetter escribió: > On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: > > Hi all. > > > > I'll make this faster. > > > > I hace this table and this function: > > You should only ever assume that your SELECT's output will have a > particular ordering when you include an ORDER BY clause that actually > specifies the order well enough :) Yeah, we went over this on the spanish list, turned out that I couldn't remember about syncscan :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
2009/5/8 David Fetter : > On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: >> Hi all. >> >> I'll make this faster. >> >> I hace this table and this function: > > You should only ever assume that your SELECT's output will have a > particular ordering when you include an ORDER BY clause that actually > specifies the order well enough :) > I test it in the first time :) With the 'order by' it works well, but in 'theory' if you run sequentially and physically a table, you expect obtain the same results with a same query. There is no indexes that can intersect the results or inherits tables. But with the option synchronize_seqscans in off, it works like i expected :) i will study a bit more this option on monday (like always). -- Emanuel Calvo Franco Sumate al ARPUG ! ( www.arpug.com.ar) ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
On Fri, May 08, 2009 at 06:10:18PM -0300, Emanuel Calvo Franco wrote: > Hi all. > > I'll make this faster. > > I hace this table and this function: You should only ever assume that your SELECT's output will have a particular ordering when you include an ORDER BY clause that actually specifies the order well enough :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
2009/5/8 Tom Lane : > Emanuel Calvo Franco writes: >> Executing 'select * from datos limit 1 offset 15' two times i have >> different >> result sets. > > The "synchronous scan" logic is probably responsible. Turn off > synchronize_seqscans if this behavior bothers you. > > regards, tom lane > It works Tom, Thanks! -- Emanuel Calvo Franco Sumate al ARPUG ! ( www.arpug.com.ar) ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] limit-offset different result sets with same query
Emanuel Calvo Franco writes: > Executing 'select * from datos limit 1 offset 15' two times i have > different > result sets. The "synchronous scan" logic is probably responsible. Turn off synchronize_seqscans if this behavior bothers you. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
