3 ms·

Has Postgres yet enabled any sort of locking of the query plan? It's been a while since I've used it at scale, but at my previous job half of our incidents wer

by pants2 3y ago

Has Postgres yet enabled any sort of locking of the query plan? It's been a while since I've used it at scale, but at my previous job half of our incidents were caused by Postgres randomly deciding to change the query plan for a call that was working just fine. Then I'd go in there and re-write the SQL a few times until it figured out what to do, rinse and repeat every few months.

natoliniak 3y ago
I'm dealing with a very large partitioned table (several millions of inserts per day) and i solve this by running `vacuum analyze` nightly which somehow solved 99% our read issues Prior to that, we tried various indexing strategies, de-normalizing some of the data, but ultimately i found that weird plan inconsistency to be solved by the vacuum job
sgarland 3y ago
Not natively. There is pg_store_plans [0] which I haven’t used, but looks interesting. Most of the time, query flips are due to inaccurate statistics over time. Try changing the default statistics target for the affected column. [0]: https://github.com/ossc-db/pg_store_plans https://github.com/ossc-db/pg_store_plans
polishdude20 3y ago
I'd love to have that. At a previous gig a query ballooned up to taking 30 seconds up from 1 second. The reason was the query planner decided to use a different plan. Any statistics update didn't work. Finally I tried just swapping the join order. The plan changed all was well. Until the planner decided to change the plan again...
timacles 3y ago
No offense guys, but just get a real DBA. Postgres doesnt just randomly do things and will always produce the same result given the same circumstances. Either you werent running routine maintenance and your statistics are messed up, or the statistics arent properly configured or your data is just poorly stored.

3 ms·

Has Postgres yet enabled any sort of locking of the query plan? It's been a while since I've used it at scale, but at my previous job half of our incidents wer

by pants2 3y ago

natoliniak 3y ago
I'm dealing with a very large partitioned table (several millions of inserts per day) and i solve this by running `vacuum analyze` nightly which somehow solved 99% our read issues Prior to that, we tried various indexing strategies, de-normalizing some of the data, but ultimately i found that weird plan inconsistency to be solved by the vacuum job
sgarland 3y ago
Not natively. There is pg_store_plans [0] which I haven’t used, but looks interesting. Most of the time, query flips are due to inaccurate statistics over time. Try changing the default statistics target for the affected column. [0]: https://github.com/ossc-db/pg_store_plans https://github.com/ossc-db/pg_store_plans
polishdude20 3y ago
I'd love to have that. At a previous gig a query ballooned up to taking 30 seconds up from 1 second. The reason was the query planner decided to use a different plan. Any statistics update didn't work. Finally I tried just swapping the join order. The plan changed all was well. Until the planner decided to change the plan again...
timacles 3y ago
No offense guys, but just get a real DBA. Postgres doesnt just randomly do things and will always produce the same result given the same circumstances. Either you werent running routine maintenance and your statistics are messed up, or the statistics arent properly configured or your data is just poorly stored.