From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: sequential scan result order vs performance |
Date: | 2016-10-30 14:12:05 |
Message-ID: | 20461.1477836725@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Andres Freund <andres(at)anarazel(dot)de> writes:
> It's quite easy to change iteration so we start with the latest item,
> and iterate till the first, rather than the other way round. In
> benchmarks with somewhat wide columns and aggregation, this yields
> speedups of over 30%, before hitting other bottlenecks.
> I do wonder however if it's acceptable to change the result order of
> sequential scans.
I think there will be a lot of howls. People expect that creating
a table by inserting a bunch of rows, and then reading back those
rows, will not change the order. We already futzed with that guarantee
a bit with syncscans, but that only affects quite large tables --- and
even there, we were forced to provide a way to turn it off.
If you were talking about 3X then maybe it would be worth it, but for 30%
(on a subset of queries) I am not excited.
I wonder whether we could instead adjust the rules for insertion so
that tuples tend to be physically in order by itemid. I'm imagining
leaving two "holes" in a page and sometimes (hopefully not often)
having to shift data during insert to preserve the ordering.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2016-10-30 15:35:21 | Re: delta relations in AFTER triggers |
Previous Message | Andres Freund | 2016-10-30 12:55:12 | Re: sources.sgml typo |