Awesome thread. Samuel,
Just wanted you to be aware of the work we're doing at TimescaleDB ( http://www.timescale.com/), a time-series database extension for PostgreSQL. Some of how we might help you: - automatic partitioning by space (primary key - like country_id, for instance) and time. This creates "chunks" of your data, right-sized by volume and time constraints (which makes inserts fast at much greater scale than Vanilla PostgreSQL - not sure if this is a problem for you) - this will also help if your queries are selective on time and country_id (per this example) - the partitioning by time allows you to DROP old chunks without the need for vacuums On Thu, Apr 20, 2017 at 8:30 AM, Vick Khera <vi...@khera.org> wrote: > I'm curious why you have so many partial indexes. Are you trying to make > custom indexes per query? It seems to me you might want to consider making > the indexes general, and remove the redundant ones (that have the same > prefix list of indexed fields). > > Secondly your table is 102Gb. Clearly there's a lot of data here. How many > rows does that take? I would further suggest that you partition this table > such that there are no more than about 10 million rows per partition (I've > done this by using a id % 100 computation). Maybe in your case it makes > sense to partition it based on the "what" field, because it appears you are > trying to do that with your partial indexes already. > > On Wed, Apr 19, 2017 at 10:11 PM, Samuel Williams < > space.ship.travel...@gmail.com> wrote: > >> Okay, so after changing longitude/latitude to float4, and >> re-organizing the table a bit, I got the query down from about 8 >> minutes to 40 seconds. >> >> The details are in the gist comments: >> https://gist.github.com/ioquatix/bddda36d9e4ffaceb7a62d7b62259121 >> >> Now, just need to get performance another 2 orders of magnitude >> better. Can we make the index a bit more coarse grained, perhaps >> convert long/lat to integers or something, use a hilbert index, use a >> postgis index... ideas? >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > -- TimescaleDB* | *Growth & Developer Evangelism c: 908.581.9509 335 Madison Ave. New York, NY 10017 www.timescale.com https://github.com/timescale/timescaledb
