News for hackers. Stuff that matters.
Re:Use SQL subqueries to count distinct 50x faster by lincolnq on Friday January 24, 2014 @03:47PM (#7117891)
Huh, interesting! I just looked up the parameter - "the planner's estimate of the cost of processing each row during a query". So increasing this will tend to cause the query planner to forgo complete table scans in favor of indexed queries more often. Increasing the parameter by a factor of 20 seems like you're telling it "never do complete table scans unless it's completely unavoidable, always use an index".

I can see why this would make the query plans look better (in that index scans make more sense to humans) but are you sure you're actually getting better performance? If you are getting better performance, why is the default so wrong? Computers are really fast at sequential scans, less so at random-access stuff.

Re:Use SQL subqueries to count distinct 50x faster by hsitz on Friday January 24, 2014 @10:10PM (#7119673)
"Computers are really fast at sequential scans, less so at random-access stuff."

This is true if you're simple talking about raw IO throughput, but in terms of algorithmic searching the truth is almost exactly the opposite. For example, to find a record in a 100 million record table with a unique key using sequential search would require on average a scan of 50 million records. To access the same record when that key is indexed requires accessing/testing a maximum 23 or 24 entries in the index (2^23 is roughly 10M) and then direct access of the exact record needed in the table. Indexes are used because they speed things up exponentially, even though it may be true that random access IO is much slower than sequential IO.

People, I suspect, also have much higher throughput when scanning sequentially compared to random access of items. But imagine trying to find the single person in a copy of the Manhattan white pages (is there still such a thing?) with the name 'Horace Greeley'. Would you prefer to scan sequentially from the beginning (no use of an index) or make use of the fact that white page phone books are indexed by lastname, firstname?

Of course, because sequential scanning is so much faster than random access it will always be faster on tiny tables. It will depend on the system and record size, but I'm guessing indexing will take over as faster method as table size grows beyond 5k records or so, give or take an order of magnitude. Not sure of exact numbers but you get the idea. As number of records goes up from there the speed advantage of indexing over scanning grows at exponential rate.

Re:Use SQL subqueries to count distinct 50x faster by corresation on Friday January 24, 2014 @05:40PM (#7118681)
The default is configured for magnetic disks, where random IO is brutally slow (on the order of 30-100 per second) and sequential reads are reasonably fast. But what if you're running it on a Micron P420m with 700,000 IOPS per second?
Re:Use SQL subqueries to count distinct 50x faster by jdub on Saturday January 25, 2014 @02:37AM (#7120188)
700,000 I/Os per second per second? That is some serious storage acceleration you've got there.
Re:Use SQL subqueries to count distinct 50x faster by Sami_Lehtinen on Sunday January 26, 2014 @08:34AM (#7125013)
With proper SSD drives you can get seriously high IOPS. https://www.fusionio.com/products/iodrive2-duo/
Re:Use SQL subqueries to count distinct 50x faster by corresation on Sunday January 26, 2014 @01:46PM (#7126230)
I think he was humorously noting that I erroneously said IOPS per second, akin to saying ATM machine, CD disc, etc.
Re:Use SQL subqueries to count distinct 50x faster by jdub on Wednesday February 05, 2014 @05:25PM (#7187284)
:-)