5 ms·

Since the PostgreSQL folks are clearly against hints, maybe another random feature suggestion: If a (read-only) query is taking "too long", maybe you could laun

Since the PostgreSQL folks are clearly against hints, maybe another random feature suggestion: If a (read-only) query is taking "too long", maybe you could launch a second copy with a different plan. Maybe even a third after twice as long and a fourth after twice as long again. Kill them all once any one returns. Trades some extra load now against waiting forever for a 'bad' query plan. The idea obviously needs work and has probably already been suggested and dismissed, somewhere, but I thought I might throw it out there, especially with modern computers having so many cores.

lgessler 5y ago
I'm not a DB expert, but aren't most queries bottlenecked by disk bandwidth? If so, addtl queries might hurt more than help.
- jandrewrogers 5y ago
  Modern databases on modern server hardware are not limited by disk bandwidth typically, though Postgres is not a modern design and struggles to use the disk bandwidth available. That said, since these queries are accessing roughly the same data, concurrent queries will mostly be hitting cache. Memory bandwidth is often a larger limitation for databases these days, so it would still be wasteful.
zepearl 5y ago
On one hand this could theoretically work if the optimizer tracks historical SQLs + the query plans used at that time + their runtime and decides to try to use a different query plan, then aborts the SQL being executed when its runtime exceeds the historical runtime. But in practice it's likely to mess things up because data volume/distribution/machine load (CPU, disks, etc) might differ compared to the past etc... . On the other hand, in general there are often too many postential combinations of query plans to try out (hundreds even for a relatively simple SQL) and trying them all out would need hours/days/etc... . The "good plan" might be something that a machine might categorize as "very unlikely to work" so it might end up being the one tested automatically at the very end. Normal hints would still be a lot easier to handle in the code and for the user.

lgessler 5y ago

I'm not a DB expert, but aren't most queries bottlenecked by disk bandwidth? If so, addtl queries might hurt more than help.

jandrewrogers 5y ago
Modern databases on modern server hardware are not limited by disk bandwidth typically, though Postgres is not a modern design and struggles to use the disk bandwidth available. That said, since these queries are accessing roughly the same data, concurrent queries will mostly be hitting cache. Memory bandwidth is often a larger limitation for databases these days, so it would still be wasteful.

zepearl 5y ago

On one hand this could theoretically work if the optimizer tracks historical SQLs + the query plans used at that time + their runtime and decides to try to use a different query plan, then aborts the SQL being executed when its runtime exceeds the historical runtime. But in practice it's likely to mess things up because data volume/distribution/machine load (CPU, disks, etc) might differ compared to the past etc... . On the other hand, in general there are often too many postential combinations of query plans to try out (hundreds even for a relatively simple SQL) and trying them all out would need hours/days/etc... . The "good plan" might be something that a machine might categorize as "very unlikely to work" so it might end up being the one tested automatically at the very end. Normal hints would still be a lot easier to handle in the code and for the user.