{"id":2923,"date":"2007-11-05T10:09:23","date_gmt":"2007-11-05T17:09:23","guid":{"rendered":"https:\/\/redfindevelop.wpengine.com\/blog\/devblog\/2007\/11\/elephant_versus_dolphin_which_is_faster_which_is_smarter.html"},"modified":"2020-10-05T13:12:51","modified_gmt":"2020-10-05T20:12:51","slug":"elephant_versus_dolphin_which_is_faster_which_is_smarter","status":"publish","type":"post","link":"https:\/\/www.redfin.com\/news\/elephant_versus_dolphin_which_is_faster_which_is_smarter\/","title":{"rendered":"Elephant versus Dolphin: Which is Faster? Which is Smarter?"},"content":{"rendered":"

\"Elephant\"<\/a> vs.\"MySQL\"<\/a><\/p>\n

Redfin recently switched some of our backend DB infrastructure from MySQL<\/a> to Postgres<\/a>, and we plan to wholly switch to Postgres in the near future. This wasn\u2019t an easy decision; MySQL has a lot going for it, and switching has been a lot of work. However, we\u2019ve already seen major benefits from choosing Postgres, and we expect to see more as we complete our transition. In particular, performance on certain geographic queries has improved dramatically.
\nA simple Google search<\/a> shows that a lot of people have already opined about MySQL versus Postgres (e.g. here<\/a>, here<\/a>, here<\/a>, here<\/a>, here<\/a>, and here<\/a>) but we weren’t able to find much information that applied directly to the problem we were having. Specifically, we were having some major performance problems with queries that were constrained by both spatial and numeric columns, and all of our attempts to squeeze more performance out of MySQL (including hiring expensive outside consultants) had come to naught.<\/p>\n

GIS Indexes<\/h2>\n

Redfin is an online real estate company, and our map based UI is the most-used part of our web site (as well as being the biggest performance hog.) When a user views the map, we use SQL to find the relevant listings or past sales. Users typically constrain a search by numerous criteria, such as maximum price or minimum square footage. Since the UI is map based, users are ALWAYS constraining by geography, though that constraint might be weak.<\/p>\n

How We Did It In MySQL<\/h3>\n

In MySQL, the queries might look something like:<\/p>\n

SELECT<\/code>
\n    *<\/code>
\nFROM<\/code>
\n    listings<\/code>
\nWHERE <\/code>
\n    price <= 400000 AND <\/code>
\n    num_bedrooms >= 2 AND <\/code>
\n    num_bathrooms >= 1.5 AND <\/code>
\n    type = 'condo' AND <\/code>
\n    MBRContains(GeomFromText('POLYGON((X1 Y1,X1 Y2,X2 Y2,X2 Y1,X1 Y1))'), centroid_col) <\/code>
\nLIMIT <\/code>
\n    101<\/code><\/p>\n

where X1\/Y1 and X2\/Y2 are lat\/long pairs that describe the region to be searched. To improve performance, we create indexes on the relevant columns. In MySQL, a normal index<\/a> cannot include spatial columns, and spatial indexes cannot include normal columns. In this example, we might have one multi-column index on price, num_bedrooms, and num_bathrooms, and another single-column index on centroid_col. In many cases, this performs great. Examples include: <\/p>\n