[HN Gopher] Pg_hint_plan: Force PostgreSQL to execute query plan...
___________________________________________________________________
Pg_hint_plan: Force PostgreSQL to execute query plans the way you
want
Author : justinclift
Score : 84 points
Date : 2024-03-15 05:02 UTC (1 days ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| justinclift wrote:
| This looks really useful. Came across it due to a comment in
| another submission:
|
| https://news.ycombinator.com/item?id=39712127
| __s wrote:
| Back when I worked on Citus I saw enough capability for this, &
| this seemed like the right way to settle demand for hints
|
| Glad to see it executed
| IvanVergiliev wrote:
| Using the hint table has been pretty painful, in my experience.
| Two main difficulties I've seen: 1. The hint patterns are
| whitespace-sensitive. Accidentally put a tab instead of a couple
| of spaces, and you get a silent match failure. 2. There are
| different ways to encode query parameters - `?` for literals in
| the query text, `$n` for query parameters. Psql / pgcli don't
| support parameterized queries so you can't use them to iterate on
| your hints.
|
| Still super useful when you have no other options though.
| IsTom wrote:
| > Psql / pgcli don't support parameterized queries so you can't
| use them to iterate on your hints.
|
| Isn't this what PREPARE and EXECUTE does?
| feike wrote:
| You should be able nowadays with PG16 INSERT
| INTO tbl1 VALUES ($1, $2) \bind 'first value' 'second value' \g
|
| https://www.postgresql.org/docs/current/app-psql.html#APP-PS...
|
| For older versions, you can do: \set v_x
| 'first value' \set v_y 'second value' INSERT
| INTO tbl1 VALUES (:'v_x', :'v_y'); \set v_x 'next
| value' INSERT INTO tbl1 VALUES (:'v_x', :'v_y');
| th3iedkid wrote:
| Isn't hinting more like asking nicely than forcing the engine to
| choose the other way?
| pm90 wrote:
| you can't really do the latter.
| FritsHoogland wrote:
| It's a bit of both. What pg_hint_plan does is change the cost
| to favour the hinted suggestion. If hinted suggestion is
| impossible, the planner will still perform something else.
| FritsHoogland wrote:
| But if 'force' means changing what it would do otherwise,
| where 'otherwise' is: a different plan having the lowest
| cost, the: yes that is exactly what it does.
| justinclift wrote:
| Yeah, I probably could have used better wording for the title
| submission.
|
| The GitHub repo's wording is broken english currently though
| which is (probably?) worse.
| ioltas wrote:
| I've been doing maintenance and bug fixes for this module for
| over 18 months now (last commit on HEAD seems to be mine),
| managing the last two releases. If you have questions and/or
| feedback, feel free.
| abhishekjha wrote:
| I am digging into postgres source code past few weeks. This
| project seems like a good way to see how plugins integrate. I
| may reach out later if I have questions
| RMarcus wrote:
| Just wanted to say thank you! This extension was critical to a
| bunch of my research (and now my lab's research as well). Being
| able to control fine-grained elements of each plan while
| letting the PG planner "do the rest" has saved me personally
| probably 100s of hours of work.
| dgan wrote:
| Is there a way to bypass SQL completely, and somehow give the
| Postgres the plan to execute? So targeting their intermediate
| language (i presume, it is not stable?) instead of the front one
| SigmundA wrote:
| MSSQL lets you save a query plan as XML then upload to another
| system as a hint. PG could do this using the json or xml output
| of EXPLAIN.
| justinclift wrote:
| That's an interesting idea. Might be a good ability for
| someone to add. :)
| U1F984 wrote:
| Not sure why this is down voted, I also looked into this at
| some point but was unable to find a solution.
| __s wrote:
| You could do it as an extension, but otherwise no
| adamzochowski wrote:
| Aren't there two arguments for why this is bad?
|
| - db will generate new plans as necessary when row counts and
| values change. Putting in hints makes the plan rigid likely
| leading to headaches down the line.
|
| - as new Postgres comes out, it's planner will do a better job.
| Again forcing specific plan might force the planner into
| optimization that no longer is optimal.
|
| In my experience developers almost never comeback to a query with
| hints to double check if hints are really needed.
|
| Famously oracle has query hints that don't do nothing no more,
| that are ignored, but oracle can't remove them from query
| language because that would break too many existing queries.
|
| I like Postgres stance that if query planner doesn't do a good
| job, then dba should first update table/column statistics, and if
| things are truly bad, submit but to Postgres so the query
| optimizer can be updated itself.
|
| Saying all that, hints support through an extension to Postgres
| is a good compromise. Postgres developers don't need to bother
| with hints, its a third party feature. And dba/users, if they
| really need hints, now they have them.
| deniska wrote:
| Why it's good: you won't get a sudden slowdown if postgresql
| for some reason changes its plan to something much less
| performant.
| silon42 wrote:
| Yup... parent point 1 is an often a misfeature in production
| (yes, sometimes the plan is better, but sometimes not).
| btown wrote:
| What about when column statistics don't accurately describe a
| subset of the table that you know from a business perspective
| is likely to have different dynamics? Allowing the client to
| essentially A/B test between different advisements and evaluate
| performance, with full knowledge of that business context, may
| be meaningful at certain scales.
| phamilton wrote:
| That's valuable, but cranking statistics up to 10000 and/or
| creating custom statistics can go a very long way to helping
| the planner understand the dataset more fully.
| justinclift wrote:
| Sure, but for production environments being able to tell
| the planner to do it the way you want (aka "fix it for
| right now") is often the overriding priority.
|
| The "make it work work perfectly from just statistics" can
| come later, when time is more flexible. Probably on an
| identical non-production copy of the environment, if that
| shows the same performance characteristics. ;)
| heavenlyblue wrote:
| Can you please explain how cranking statistics 10000 and/or
| creating custom statistics is a more straightfoward way to
| enforce a certain query plan VS telling the databsee which
| plan should be used instead?
| nextaccountic wrote:
| > In my experience developers almost never comeback to a query
| with hints to double check if hints are really needed.
|
| What's needed, then, is a benchmark suite that tests if those
| hints still give a better performance than a hintless query
| ahachete wrote:
| > Aren't there two arguments for why this is bad?
|
| > - db will generate new plans as necessary when row counts and
| values change. Putting in hints makes the plan rigid likely
| leading to headaches down the line.
|
| > - as new Postgres comes out, it's planner will do a better
| job. Again forcing specific plan might force the planner into
| optimization that no longer is optimal.
|
| It depends what is your priority. Most production environments
| want to favor predictability over raw performance. I'd rather
| trade 10% performance degradation in average for a consistent
| query performance.
|
| Even if statistics or new versions could come with 10% better
| plans, I prefer that my query's performance is predictable and
| does not experience high p90s or even worse that you risk
| experiencing plan flips that turn your 0.2s 10K/qps query into
| a 40s query.
| williamdclt wrote:
| > I like Postgres stance that if query planner doesn't do a
| good job, then dba should first update table/column statistics,
| and if things are truly bad, submit but to Postgres so the
| query optimizer can be updated itself.
|
| That's a very unhelpful stance when I'm having an incident in
| production because PG decided to use a stupid query plan.
| Waiting months - years for a bugfix (which might not even be
| backported to my PG version) is not a solution.
|
| I agree that hints are a very dangerous thing, but they're
| invaluable as an emergency measure
| phamilton wrote:
| I like it as an emergency measure, but I often see them used
| when there's a shallow understanding of operating the db.
|
| Before using a hint or rewriting a query to force a specific
| plan, I try and push the team to do these things:
|
| 1. Run `vacuum analyze` and tune the auto vacuum settings.
| This fixes issues surprisingly often. 2. Increase statistics
| on the table. 3. Tweak planner settings globally or just for
| the query. Stuff like `set local join_collapse_limit=1` can
| fix a bad plan. This is pretty similar to hinting, so not a
| huge argument that this is better beyond not requiring an
| extension.
| franckpachot wrote:
| All those methods are try and guess. With hints you can
| have a scientific approach to understand why the bad plan
| has been chosen and find the right plan. Then, you can
| address the root cause. join_collapse_limit=1 may set the
| join order but not the join direction, so that's not enough
| if cardinality is misestimated. And pg_hint_plan can set
| this parameter for one statement if that's what you want,
| better than setting for the transaction
| Yeroc wrote:
| In a database supporting several applications or even just
| a large data model it can be rather difficult to ensure a
| global setting to the query planner doesn't cause a
| regression to other queries. A query hint can be a nice way
| to quickly solve a performance issue without risk of
| regressing elsewhere. Agree they should be used as a last
| resort and as a sort term fix while a better, longer term
| fix is investigated but they are a critical tool to have in
| your tool box especially as postgres moves into the
| business critical domains occupied by the commercial
| database vendors.
| phamilton wrote:
| `set local` allows tuning query planner settings scoped
| to a transaction, so you don't have to do it globally.
| tempest_ wrote:
| I have been burned before when a query on a newer db version
| was "optimized" in a different way that caused performance to
| drop but I have not yet had a query on the same version vary
| so drastically as to cause a problem.
|
| Is this a more common occurrence that I just have not
| encountered before?
| williamdclt wrote:
| Not common (if it was anything more than exceptional, PG
| wouldn't be a production ready product) but it happens:
| https://www.postgresql.org/message-
| id/17540-7aa1855ad5ec18b4...
| jandrewrogers wrote:
| It is fairly common in e.g. multi-terabyte Postgres
| databases. Beyond a certain scale, it can become impossible
| for the statistics collector to build an accurate model for
| large tables. When this happens, you can end up in the
| strange place where the query plans change every time you
| run analyze even though the data model and data
| distribution hasn't changed. I've seen operational issues
| at multiple companies due to the query plans randomly
| changing like this on large tables in Postgres.
| perrygeo wrote:
| More philosophically, the query planner is a finely-tuned piece
| of engineering. If your mental model disagrees with it ("Why
| isn't it using the index I want?") then it's highly likely your
| mental model is wrong.
|
| Your data model might contain obvious mistakes. Statistics can
| be out of date too, if e.g. a table was bulk loaded and never
| analyzed. `ANALYZE tablename` done. Sometimes removing unused
| indexes can improve things. TLDR; it's always something that
| you need to tune in your own database. When in doubt, the query
| planner is right and you are wrong. Good engineering means
| having the intellectual curiosity to exhaust these possibility
| before resorting to hints.
|
| Hints are an extreme measure. You're basically saying that you
| know better than the query planner, for now until eternity, and
| you choose to optimize by hand. That may be the case but it
| requires detailed knowledge of your table and access patterns.
| The vast majority of misbehaving query plans just need updated
| statistics or a better index.
| jupp0r wrote:
| Queries are complex and the query planner is using heuristics
| that might just not fit your situation in some cases. The
| query planner is great for 99.99% of queries but a super
| small number of edge cases will need tuning at the same time.
|
| Finding out what went wrong in the query plan by looking at
| optimizer traces is a lot of work. I did so recently and the
| trace alone was 317MB.
| jandrewrogers wrote:
| This is not a correct assumption with Postgres. Its
| statistics collection process has fundamental flaws when
| tables become large such that the statistics it uses for
| optimization are in no way representative of the actual data,
| leading to the situation this thread is about.
|
| Statistics collection is a weak spot in Postgres and query
| optimization relies on that information to do its job.
| bsdpufferfish wrote:
| > you know better than the query planner, for now until
| eternity,
|
| Nope, you just have to know it's fixing a real problem today.
|
| Having a query regress in performance below a KPI would be
| worse than not taking advantage of a further optimization in
| the future, due to out of date hint.
|
| > Good engineering means having the intellectual curiosity to
| exhaust these possibility before resorting to hints
|
| Why is that better?
|
| Luckily we don't have to rely on such grandiose claims. Just
| try it out. If you find a query that you can tune better than
| the planner for your data set, then it's a better outcome.
| heavenlyblue wrote:
| > You're basically saying that you know better than the query
| planner, for now until eternity, and you choose to optimize
| by hand.
|
| Yes I do know better what data is going to be in the database
| I am managing that some heuristic mechanism.
| justinclift wrote:
| > Good engineering means having the intellectual curiosity to
| exhaust these possibility before resorting to hints.
|
| While it's great that "good engineering" exists, it often
| requires a bunch more time and effort than people have for
| the task right then.
|
| Being inflexible and always demanding that time is taken is
| an extremely poor approach, and often doesn't lead to a good
| quality result.
|
| Instead it often leads to the inflexible thing being retired
| and a more flexible alternative being used from then on.
| RaftPeople wrote:
| > _Hints are an extreme measure. You 're basically saying
| that you know better than the query planner_
|
| Absolutely.
|
| A query planner does not analyze the complete and precise
| solution space, none of them do. The query planner will be
| extremely wrong sometimes. The only logical solution is to
| provide a mechanism to guide the planner towards the correct
| solution.
|
| I've worked with DB2, Oracle and MS SQL Server over the last
| 3 decades and the story is always the same, the planner is
| not perfect.
| CuriouslyC wrote:
| Those points are academically correct. In the real world you
| can end up with the query planner doing very boneheaded stuff
| that threatens to literally break your business when there's
| another approach that's fast it just seems to miss. In these
| cases hints would be a lot easier than the stuff you end up
| doing.
| jandrewrogers wrote:
| That's the theory but it does not work this way in practice.
|
| Many of the issues people run into with the Postgres query
| planner/optimizer are side effects of architectural
| peculiarities with no straightforward fix. Some of these
| issues, such as the limitations of Postgres statistics
| collection, have been unaddressed for decades because they are
| extremely difficult to address without major architectural
| surgery that will have its own side effects.
|
| In my opinion, Postgres either needs to allow people to
| disable/bypass the query planner with hints or whatever, or
| commit to the difficult and unpleasant work of fixing the
| architecture so that these issues no longer occur. This
| persistent state of affairs of neither fixing the core problems
| nor allowing a mechanism to work around them is not user
| friendly. I like and use Postgres but this aspect of the
| project is quite broken.
|
| The alternative is to put a disclaimer in the Postgres
| documentation that it should not be used for certain types of
| common workloads or large data models; many of these issues
| don't show up in force until databases become quite large.
|
| This is a chronic source of pain in large-scale Postgres
| installations.
| pgaddict wrote:
| I don't know which "architectural peculiarities" you have in
| mind, but the issues we have are largely independent of the
| architecture. So I'm not sure what exactly you think we
| should be fixing architecture-wise ...
|
| The way I see it the issues we have in query
| planning/optimization are largely independent of the overall
| architecture. It's simply a consequence of relying on
| statistics too much. But there's always going to be
| inaccuracies, because the whole point of stats is that it's a
| compact/lossy approximation of the data. No matter how much
| more statistics we might collect there will always be some
| details missing, until the statistics are way too large to be
| practical.
|
| There are discussions about considering how "risky" a given
| plan is - some plan shapes may be more problematic, but I
| don't think anyone submitted any patch so far. But even with
| that would not be a perfect solution - no approach relying
| solely on a priori information can be.
|
| IMHO the only way forward is to work on making the plans more
| robust to this kind of mistakes. But that's very hard too.
| bsdpufferfish wrote:
| What if I have a performance problem I need to fix now?
| RMarcus wrote:
| This is the main motivation behind learned "steering" query
| optimizers: even if a DBA finds the right hint for a query, it
| is difficult to track that hint through data changes, future DB
| versions, and even query changes (e.g., if you add another
| condition to the WHERE clause, should you keep the hints or
| drop them?). Turns out, with a little bit of systems work, ML
| models can do a reasonably good job of selecting a good hint
| for a particular query plan.
| staticlibs wrote:
| What is cool about Postgres extensions ecosystem, is that new
| functionality can be built upon existing extensions. For example,
| Babelfish (SQL Server compatibility layer for Postgres) uses
| pg_hint_plan to implement Transact-SQL hints support [1]. This is
| really useful, because existing queries, that were optimized for
| MSSQL, may require tuning when moved to Postgres/Babelfish, and
| it is great to have additional knobs for tuning.
|
| [1] https://github.com/babelfish-for-postgresql/babelfish-for-
| po...
| nextaccountic wrote:
| Is this like MSSQL's WITH(INDEX())? [0] (Does PostgreSQL have
| this? It seems it doesn't [1])
|
| [0] https://stackoverflow.com/questions/6593765/how-to-use-
| index...
|
| [1] https://stackoverflow.com/questions/309786/how-do-i-force-
| po... - and
| https://www.2ndquadrant.com/en/blog/hinting_at_postgresql/ says
| why Postgres devs think this is a bad feature
| williamdclt wrote:
| It doesn't exist indeed. I understand the PG maintainers
| stance, but I can't agree with it.
|
| First because "we get bugs fixed quickly" is true but not true
| enough (my own experience: https://www.postgresql.org/message-
| id/17540-7aa1855ad5ec18b4... has not been fixed since reported
| 1.5y ago)
|
| Second because all the knobs available to tune the query
| planner (listed in your last link) are obscure and require deep
| understanding of Postgres internals to make sense of them,
| which means that only a fraction of people understand them
| whereas query plans are more pedestrian. They're also often
| database-scoped or session-scoped rather than query-scoped,
| it's a huge risk of making things worse rather than better.
| phamilton wrote:
| All the knobs for query planning and cost estimation can be
| scoped to the transaction with `set local <key>=<value>`.
| franckpachot wrote:
| Even if bugs are fixed instantly, nobody will apply a patch
| in production withiut previous testing. Changing system-wide
| behavior to fix a single query may make things worse. Hints
| are the only way to fix at the scope of one statement with
| the guarantee that it doesn't break others
| RaftPeople wrote:
| > _I understand the PG maintainers stance, but I can't agree
| with it_
|
| I don't just not agree, I truly don't understand how they can
| take that position knowing the nature of the problem.
|
| "The problem"=optimizing over a large space in limited time
| without complete info. It will never be perfect, there will
| always be exceptions.
| mattrighetti wrote:
| Not related to the project but do you know of useful content that
| explains how to approach query optimization for Postgres? All I
| was able to find was classic stuff like `explain analyze` etc.
| phamilton wrote:
| https://www.pgmustard.com/ is a great tool. There are some free
| alternatives for the actual tool, but their documentation is
| pretty great. For example,
| https://www.pgmustard.com/docs/explain/heap-fetches provides
| clarity that isn't obvious from the official pg docs.
|
| Beyond those resources, here are a few useful things I've
| learned: 1. `explain (analyze, buffers)` is useful. It will
| tell you about hot vs cold data. One caveat: it doesn't
| deduplicate the buffer hits, so 1M buffer hits could be only a
| few thousand unique pages. But I still find it useful
| especially when comparing query plans. 2. pg_buffercache.
| Knowing what's in the buffer allows you to optimize the long
| tail of queries that perform buffer reads. Sometimes rebuilding
| an index on an unrelated table can create space in the buffer
| for the data the query needs. 3. Try using dedicated covering
| partial indexes for high traffic queries. An index-only scan is
| super cheap and with the right include and where condition you
| can make it small and efficient.
|
| The tips above are especially useful in Aurora, where the
| shared buffers are huge (there's no page cache so it's the only
| caching layer).
| iurisilvio wrote:
| The PEV2 is open source and give you a good visualization. I
| never used this pgmustard to compare.
|
| https://explain.dalibo.com/
| zac23or wrote:
| My biggest problem with Postgres is the Planner/Optimizer. The
| worst example is a super simple query:
|
| SELECT * FROM TABLE FOO WHERE ORGANIZATION_ID = 10 ORDER BY
| CREATED_AT LIMIT 10 OFFSET 0;
|
| Postgres sorts by created_at using an index, but uses the filter
| on organization_id. This organization has a million rows...
| without the order, the query runs in ms. In order, in
| seconds/minutes.
|
| Sometimes the plan changes if you change the query offset!
|
| For organization 10, the plane is goood, for organization 11, the
| plane is bad...
|
| Change stats, add more stats, fill the void. that doesn't solve
| anything. The solution is to use a calc on order by , but in the
| end you need to create an index using Organization_id and
| created_at to really fix it.
|
| I did the same test on SQLServer and SQLite, they executed the
| query correctly, using the correct index. I've never created an
| index to fix a bad plan in SQLServer, and I've used SqlServer a
| lot more than Postgres.
|
| And I use a Pass database, it's impossible to install
| extensions...
|
| I'm going to test this extension locally, if it works I'll
| convince the company to use it (by changing the database
| provider)
| Izkata wrote:
| This sounds like what I described yesterday:
| https://news.ycombinator.com/item?id=39713239
|
| In addition to what I described there, the way you described
| it, it sounds like you don't have a single index that covers
| both columns. That may also help if I understood right.
| zac23or wrote:
| > it sounds like you don't have a single index that covers
| both columns.
|
| Yes. But other databases (SQLlite, etc.) select the correct
| index, without the need to create an index with both columns.
| riku_iki wrote:
| > For organization 10, the plane is goood, for organization 11,
| the plane is bad...
|
| that's probably because for 11, there will be more results (say
| 10k), and optimizer decided that it is faster to scan already
| sorted index with filter, than sort 10k rows.
|
| Logic of optimizer is usually reasonable, my problem with it is
| that it relies on cost config parameters, which I think by
| default are inadequate for modern servers, it is not clear how
| to set it correctly, and actual optimizer rules are not
| documented, but one need to look at PgSQL code to understand
| them.
| zac23or wrote:
| > Logic of optimizer is usually reasonable
|
| I agree. My problem is that SQLite, an embedded database,
| seems to have a better Planner/Optimizer than Postgres.
|
| Is the development of Planner/Optimizer abandoned?
|
| > my problem with it is that it relies on cost config
| parameters
|
| And I don't have access to change it, as it's a PASS
| database...
| pgaddict wrote:
| Abandoned? Certainly not. But it's a complex part of a
| mature database product, with many existing
| deployments/users, which means the improvement is affecting
| literally everyone. And query planning in general is a hard
| problem. So it takes time to get new stuff in.
| matthewbauer wrote:
| That sounds like the pg stats are messed up somehow. Try
| running explain analyze foo to see if it changes things.
|
| Postgres doesn't automatically create indexes - I bet the
| others have some implicit index on organization_id, created_at.
| Otherwise they would be giving bad results in other cases. I
| don't see why you can't just make that index in postgres.
| zac23or wrote:
| >Pg stats are messed up somehow. Try running explain analyze
| foo
|
| I did
|
| > Postgres doesn't automatically create indexes
|
| Both fields have indexes (created manually). Postgres is
| incorrectly choosing the index in order by column, not the
| index in the where clause.
| AaronFriel wrote:
| Postgres' query planner is likely considering random vs
| sequential page costs, and preferring an index scan on
| created_at.
|
| What are the current values of `random_page_cost` and
| `seq_page_cost`? SHOW seq_page_cost;
| SHOW random_page_cost;
|
| The default is typically 4, and in practice with modern disks
| you should use a lower value closer to 1.
| zac23or wrote:
| seq_page_cost is 1; random_page_cost is 2;
|
| I have many other problems with the planner, this is the most
| absurd due to the simplicity of the query.
| zacmps wrote:
| On an SSD I'd drop random to 1-1.2.
| franckpachot wrote:
| If changing random_page_cost from 4 to 2 makes a
| difference, then probably there are no good indexes. The
| choice between Seq Scan and Index Scan should be obvious
| without depending on small adjustments or one day, with
| slightly different data distribution the plan will flip to
| a bad one
| pgaddict wrote:
| It's really hard to say why this is happening without EXPLAIN
| ANALYZE (and even then it may not be obvious), but it very much
| seems like the problem with correlated columns we often have
| with LIMIT queries.
|
| The optimizer simply assumes the matching rows are "uniformly"
| distributed, because limit is costed as linear approximation of
| the startup/total cost of the input node. For example, consider
| this made up query plan Limit (cost=0.0..100.0
| rows=10) -> Index scan (cost=0.0...10000000.0
| rows=1000000) Filter: dept_id=10
|
| The table may have many more rows - say, 100x more. But the
| optimizer assumes the rows with (dept_id=10) are distributed in
| the index, so it can scan the first 1/100000 if the index to
| get the 10 rows the limits needs. So it assumes the cost is
| 10*(0+10000000)/1000000.
|
| But chances are the dept_id=10 rows happen to be at the very
| end of the index, so the planner actually needs to scan almost
| the whole index, making the cost wildly incorrect.
|
| You can verify this by looking how far the dept_id rows are in
| "order by created_id" results. If there are many other rows
| before the 10 rows you need, it's likely this.
|
| Sadly, the optimizer is not smart enough to realize there's
| this risk. I agree it's an annoying robustness issue, but I
| don't have a good idea how to mitigate it ... I wonder what the
| other databases do.
| zac23or wrote:
| > It's really hard to say why this is happening without
| EXPLAIN ANALYZE
|
| I can run EXPLAIN and paste it here, but it's just one of the
| problems with the planner (in a very basic query), I have
| many other problems with it.
|
| On the other hand, I've never had any other serious problems
| with Postgres. It's a very good database.
| justinclift wrote:
| > I can run EXPLAIN and paste it here ...
|
| Might as well, could turn up something useful. :)
| pgaddict wrote:
| I'd suggest reporting it to pgsql-performance mailing
| list [1], and continuing the discussion there. I'd expect
| more community members to join that discussion there.
|
| [1] https://www.postgresql.org/list/pgsql-performance/
| wiredfool wrote:
| One thing that I've run across is when doing a select on a
| large table with serial or indexed timestamps, ordered by
| that column is that the plans are horrible when the criteria
| you're using are unexpectedly rare, even if there's an index
| on that column. e.g. If col foo has low cardinality, with a
| bunch of common but a few rare entries, the planner will
| prefer using the ordering index to the column index even for
| the very low frequency ones.
|
| Partial indexes can be really useful there, as well as order
| by (id+0). But it's a total hack.
| dventimi wrote:
| I've not been able to reproduce this behavior. Maybe someone
| else will have better luck. create unlogged
| table foo (id int primary key generated by default as identity,
| organization_id int default floor(random()*10), created_at
| timestamptz default timestamp '2010-01-01' +
| random()*(timestamp '2020-01-01' - timestamp '2010-01-01'));
| create index on foo (created_at); create index on
| foo (organization_id); alter table foo add column
| updated_at timestamptz null default '2010-01-01
| 00:00:00'::timestamp without time zone + random() *
| ('2020-01-01 00:00:00'::timestamp without time zone -
| '2010-01-01 00:00:00'::timestamp without time zone);
| create index on foo (updated_at); insert into foo
| (id) select generate_series(1, 1e8); cluster foo
| using foo_created_at_idx; vacuum analyze;
| explain analyze select * from foo where organization_id = 1
| order by updated_at limit 10;
| QUERY PLAN
| ---------------------------------------------------------------
| ---------------------------------------------------------------
| ----------------- Limit (cost=0.57..169.50 rows=10
| width=24) (actual time=0.088..0.242 rows=10 loops=1)
| -> Index Scan using foo_updated_at_idx on foo
| (cost=0.57..173267047.88 rows=10256672 width=24) (actual
| time=0.086..0.237 rows=10 loops=1) Filter:
| (organization_id = 1) Rows Removed by Filter: 73
| Planning Time: 0.168 ms Execution Time: 0.269 ms
| foldr wrote:
| I've often wanted (or thought that I wanted) something that works
| similarly to a SQL database but where you write queries at about
| the same level of abstraction as the output of EXPLAIN. SQL is
| great for generating ad-hoc reports, or in other contexts where
| performance isn't critical. But in a production environment you
| can't always rely on it to perform as expected.
| whartung wrote:
| Back in the day, we were use a SQL db on the PC.
|
| The planner wasn't particularly sophisticated in those days,
| nor was the data volume crushing.
|
| But in the end, we would take all of our queries, and check
| their plans. We would pad the db with data until the
| combination of data and statistics and the planner all did what
| we wanted.
|
| Once we were happy with the statistics and the results we'd
| reset the database with "empty" data for the customer, and
| never touch the statistics again.
|
| We were mostly trying to eliminate table scans on simple
| queries and joins, and influencing index selection.
___________________________________________________________________
(page generated 2024-03-16 23:02 UTC)