Matthew Wakeling wrote:
Thomas, the order of columns in the index matters. The index is basically a tree structure, which resolves the left-most column before resolving the column to the right of it. So to answer your query, it will resolve the city_id, then it will have to scan almost all of the tree under that, because you are not constraining for street_id. A much better index to answer your query is (city_id, house_id, floor_id) - then it can just look up straight away. Instead of the index returning 200000 rows to check, it will return just the 2000.
Thats something I was a bit unsure about, because of the cardinality of the data. But thanks, I will try it. Just need to populate a new data base with the new index. (Apparently, creating a new index on an already existing database is slower than just recreating the db, when the db is 250GB big)
thomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
