I have a postgis table in a geodjango application that contains around 45,000 rows with a multipolygon geometry type. I would like to know if querying this table is slowed by the presence of the geometry when the geometry is not involved in the query, or in other words, if I related the geometry field with a one-to-one relationship would this boost performance?
1 Answers
That depends.
If the geometry is large enough to be stored out of line in a TOAST table, the main table will be almost as small as if it didn't have the geometry column, and it will be difficult to measure a difference in speed.
If the geometry is smaller, and the geometry is stored in the main table, querying the table will be somewhat slower, because the whole table row is read from disk and you end up having more I/O than if you didn't have the column.
However, if you store the geometry in another table with a one-to-one relationship, retrieving the geometry will incur the overhead of an extra join, which will make that operation slower.
I recommend that you keep things simple and store the geometry in the table. Very likely, the performance gain you can expect from doing something more complicated is not big, and the overall performance of your application may even suffer. This sounds to me like a premature micro-optimization. If you want to go that way, run a realistic performance test with realistic test data to verify that it is worth the effort.
Related