4 ms·

It seems like the first question to ask is, where is the query planner falling down? What are you finding in those EXPLAIN ANALYZEs that is wrong? When you sa

It seems like the first question to ask is, where is the query planner falling down? What are you finding in those EXPLAIN ANALYZEs that is wrong? When you say "that isn't the way it seems to want to allocate resources," what does that mean - do you mean you want it to auto-create indexes? SQL Server used to have a tool to suggest indexes based on a query workload, is that what you mean? Have you used https://explain.depesz.com/ https://explain.depesz.com/ ?

thom 10y ago
Yes, I spend a large part of my day looking at explain output, often on that site. The SQL Server Database Engine Tuning Advisor is reasonably good, and something like that for Postgres would be great, but ultimately it doesn't seem to look at a query and come up with a semantically identical but structurally better way of phrasing it - it will never fix unnecessary big-O performance issues (in the way that, for example, posting a single query on Stackoverflow might). But even if such a tool exists for Postgres, I'd be running it _all the time_. I'm just naively wondering if technology has moved on. I have basically no writes outside an ETL process, much of the time this is effectively single-user ad-hoc stuff. I realise I might be being hopelessly naive, but it feels like I can clearly specify a query, and I have complete control over my schema. Something must exist that requires me to pay more money but ultimately care less about endless performance tweaks to create a view that is fast?
- auganov 10y ago
  So your queries are very diverse in their nature in an unpredictable way? I mean I'd assume you'd more or less know the big-Os for the stuff you're doing and have a vague idea if there's really room for improvement? At just 35m records I'd assume you're purely cpu bound with vanilla high-order polynomial queries. Maybe what you really want is to rethink what you want the db to do. Perhaps you can take a bit more of a soft computing approach and settle for imprecise answers.
- al2o3cr 10y ago
  If you're doing sufficiently focused sorts of queries - the question mentioned "find the path from A to B to C" - then you might get value out of a DB specifically focused on that question (a graph DB, or a time-series one). The Postgres FDW stuff may help you make integrating one of those less painful. Before you do that, though: have you thrown RAM at the thing? 35m events sounds like a lot to humans, but not when you can rent machines with 1.2TB of RAM by the hour...
  - thom 10y ago
    I was more thinking of spatial stuff when I talked about paths, and PostGIS is a pretty productive platform for me so far. Example calculations would be taking the geodesic distance moved over a series of events over the total length of the path to calculate a score for 'directness'. PostGIS has aggregates to create lines from points, measure the total length etc.

thom 10y ago

Yes, I spend a large part of my day looking at explain output, often on that site. The SQL Server Database Engine Tuning Advisor is reasonably good, and something like that for Postgres would be great, but ultimately it doesn't seem to look at a query and come up with a semantically identical but structurally better way of phrasing it - it will never fix unnecessary big-O performance issues (in the way that, for example, posting a single query on Stackoverflow might). But even if such a tool exists for Postgres, I'd be running it _all the time_. I'm just naively wondering if technology has moved on. I have basically no writes outside an ETL process, much of the time this is effectively single-user ad-hoc stuff. I realise I might be being hopelessly naive, but it feels like I can clearly specify a query, and I have complete control over my schema. Something must exist that requires me to pay more money but ultimately care less about endless performance tweaks to create a view that is fast?

auganov 10y ago
So your queries are very diverse in their nature in an unpredictable way? I mean I'd assume you'd more or less know the big-Os for the stuff you're doing and have a vague idea if there's really room for improvement? At just 35m records I'd assume you're purely cpu bound with vanilla high-order polynomial queries. Maybe what you really want is to rethink what you want the db to do. Perhaps you can take a bit more of a soft computing approach and settle for imprecise answers.
al2o3cr 10y ago
If you're doing sufficiently focused sorts of queries - the question mentioned "find the path from A to B to C" - then you might get value out of a DB specifically focused on that question (a graph DB, or a time-series one). The Postgres FDW stuff may help you make integrating one of those less painful. Before you do that, though: have you thrown RAM at the thing? 35m events sounds like a lot to humans, but not when you can rent machines with 1.2TB of RAM by the hour...
- thom 10y ago
  I was more thinking of spatial stuff when I talked about paths, and PostGIS is a pretty productive platform for me so far. Example calculations would be taking the geodesic distance moved over a series of events over the total length of the path to calculate a score for 'directness'. PostGIS has aggregates to create lines from points, measure the total length etc.