On 9 October 2011 11:51, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: > 2011/10/9 Thom Brown <thom@xxxxxxxxx>: >> On 9 October 2011 04:35, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >>> 2011/10/8 Thom Brown <thom@xxxxxxxxx>: >>>> On 8 October 2011 21:13, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >>>>> 2011/10/8 Thom Brown <thom@xxxxxxxxx>: >>>>>> On 8 October 2011 19:47, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >>>>>>>>> I did it. It is strange, so your times are significantly slower than I >>>>>>>>> have. Have you enabled asserts? >>>>>>>> >>>>>>>> The table contains 15 million rows with column values randomly >>>>>>>> selected from the 1-350 range, with 60% within the 1-50 range, and >>>>>>>> asserts are enabled. >>>>>>>> >>>>>>> >>>>>>> Now I repeated tests on litlle bit wide table with 9 milion rows, but >>>>>>> without success. >>>>>>> >>>>>>> Try to disable asserts. I am not sure, but maybe there significantlly >>>>>>> change a speed. >>>>>> >>>>>> Okay, here you go. Results with debug_assertions = false: >>>>>> >>>>>> Index-only scan: 173.389 ms (78.442 ms) >>>>>> Index scan: 184239.399 ms (previously 164882.666 ms) >>>>>> Bitmap scan: 159354.261 ms (previously 154107.415 ms) >>>>>> Sequential scan: 134552.263 ms (previously 121296.999 ms) >>>>>> >>>>>> So no particularly significant difference, except with the index-only >>>>>> scan (which I repeated 3 times and it's about the same each time). >>>>> >>>>> what is size of table? >>>> >>>> 4884MB >>> >>> It has a sense - index only scan it is faster (and significantly >>> faster) on wider tables - or tables with strings where TOAST is not >>> active. Maybe there is a some issue because on thin tables is slower >>> (and I expect a should be faster everywhere). >> >> No, that's my point, I re-tested it on a table with just 2 int >> columns, and the results are roughly the same. I added all the >> columns to make it expensive to fetch the column being queried. > > then I don't understand Well here's some more reliable results since each has been run 3 times. The row size in this sample is a 10th of previous ones (i.e. 1.5 million rows): -- Narrow table (table with 2 int columns) -- Assertions = on Index-only scan = 6.088, 5.885, 6.361 Index scan = 65.661, 63.441, 64.105 Bitmap scan = 68.448, 66.476, 64.626 Sequential scan = 244.129, 239.584, 242.680 Assertions = off Index-only scan = 6.710, 6.709, 6.192 Index scan = 66.838, 67.534, 64.348 Bitmap scan = 75.662, 64.500, 69.080 Sequential scan = 232.065, 231.366, 231.547 -- Wide table (table as described in earlier post) -- Assertions = on Index-only scan = 7.313, 7.299, 6.401 Index scan = 193.555, 186.564, 198.150 Bitmap scan = 199.082, 204.664, 207.902 Sequential scan = 643.765, 645.426, 621.150 Assertions = off Index-only scan = 7.569, 6.477, 6.113 Index scan = 197.332, 197.286, 204.257 Bitmap scan = 207.838, 202.235, 203.322 Sequential scan = 653.739, 633.309, 619.081 Bitmap scans tend not to be faster than index scans in this scenario. These numbers are produced by HEAD as of this morning. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
