Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres is eating the database world

medium.com

Post reply on HN
Print view View original

11–20 of 147 posts

Re: Postgres is eating the database world

#11

Post by Minor49er »

donatj wrote: post #10
hipadev23 wrote: post #9

Earlier quoted context omitted.

MySQL offers this via use/force index (…). Similar problem where the QP will inexplicably decide one day to make a query really slow and you gotta override it.

We have a couple queries where using the correct index they'll take milliseconds, using the wrong index it'll take minutes - and mysql occasionally enough to be a noticeable load on the database chooses the wrong one and we've specified "USE INDEX" even though I really hate having to do so.

I'm curious about the query and schema that you're using

Re: Postgres is eating the database world

#12

Post by justinclift »

Vonng wrote: post #6
monero-xmr wrote: post #2

Postgres is simply the best. One thing I would like however is the ability to have control over the query planner for specific tasks. There is a dark art to influencing the query planner, but essentially it is unpredictable, and postgres can get it consistently wrong in certain scenarios. If you could just enable a special query mode that gives you absolute control over the QP for that query, it would solve a major p…

pg_hint_plan —— Give PostgreSQL ability to manually force some decisions in execution plans. https://github.com/ossc-db/pg_hint_plan

Cool, that looks super useful. Hadn't come across it before. :)

---

Just did a HN submission for it (https://news.ycombinator.com/item?id=39712211) now too.

Re: Postgres is eating the database world

#13

Post by teaearlgraycold »

RedShift1 wrote: post #7
teaearlgraycold wrote: post #3

Earlier quoted context omitted.

I’m currently learning the basics of this. Currently struggling with multiple similar scenarios where switching from a left to an inner join, or any equivalent, kills performance. But these are aggregation queries so there are only 5 records returned. I could just filter in my app code no problem. But why the hell does adding “where foo.id is not null” in SQL make it O(N*M)??? CTEs are not helping.

Have you checked with EXPLAIN ANALYZE VERBOSE?

Yup, used a nice EXPLAIN GUI tool as well to try and help.

Re: Postgres is eating the database world

#14

Post by jaktet »

monero-xmr wrote: post #5
jaktet wrote: post #4

Earlier quoted context omitted.

I haven’t used Postgres but is this the issue you’re talking about? https://github.com/launchbadge/sqlx/pull/1539

No this is a fundamental concept in postgres. If you do EXPLAIN ANALYZE on a query, you get the query plan, which is influenced by the query, indexes, table structure, etc. But the QP may decide to do a silly thing like a sequential scan where a better path exists, and adding an index to avoid the scan would be cost prohibitive. So if you could just override the QP and say "Use this index and do this type of sort and…

Oh that’s unfortunate, thanks for explaining it. Postgres has been in my list to check out but haven’t done any personal projects that I’d need it for… yet

Re: Postgres is eating the database world

#17

Post by Izkata »

teaearlgraycold wrote: post #13
RedShift1 wrote: post #7

Earlier quoted context omitted.

Have you checked with EXPLAIN ANALYZE VERBOSE?

Yup, used a nice EXPLAIN GUI tool as well to try and help.

Every time stuff like this comes up I wonder how much the people having issues would be willing to share - because every time I've fought with the postgres query planner, it eventually turned out what I wanted to do had massively worse performance* because of something I didn't take into account that postgres did. And each time, once I learned what that thing was, I was able to fix it the right way and get the query plan I was expecting, but also with the performance I was expecting.

* Usually I've been able to force query plans by disabling whole operations for a session, such as disallowing "sort" to make it use an index. The real fix in this case, for example, was to use CLUSTER to re-order data on disk, so the correlation statistic was close to 1 and postgres wanted to use the index instead of table scan + sort.

Re: Postgres is eating the database world

#18

Post by thrixton »

issung wrote: post #16

Alright I'm convinced, I'm using postgres for my next project. Anyone have any experiences with it and Entity Framework Core?

Well supported in EF core, great experience. Having said that, I’ve not used EF core with any other DB so have nothing to compare to.

Re: Postgres is eating the database world

#19

Post by TOMDM »

Postgres is such a great tool.

The feature I'd love to see added that has been kicking around the mailing list for ages now would be incremental view maintenance.

Being able to keep moderately complex analysis workloads fresh in realtime would be such a boon.

Post reply on HN