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

Speedup of deletes on PostgreSQL

ivdl.co.za

Post reply on HN
Print view View original

31–40 of 64 posts

Re: Speedup of deletes on PostgreSQL

#31

Post by dagss »

What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan.

That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple.

In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned something. Would be so much easier to newcomers... instead of a giant footgun and obscure lore that only becomes evident after you have a oot of data in production.

Google Data Store does this, just assumes that _of course_ you did not look to do a full table scan. Works great. Not SQL, but no reason popular SQL DBs could not have an option to have query planners throw errors at certain points instead of always making a plan no matter how bad.

SQL has a reputation for a steep learning curve and I blame this single thing -- that you get a poor plan instead of an error -- a lot for it.

Re: Speedup of deletes on PostgreSQL

#32

Post by John23832 »

dagss wrote: post #31

What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple. In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned…

> What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan.

You can run ANALYSE on Postgres.

Re: Speedup of deletes on PostgreSQL

#34

Post by dagss »

John23832 wrote: post #32
dagss wrote: post #31

What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. That way a lot of SQL index creation -- something considered a black art by surprisingly many -- would just be prompted by test suite failures. If you don't have the right indices, your test fails. Simple. In this case, have TestDeleteCustomer fail, realize you need to add index, 5 minutes later done and learned…

> What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. You can run ANALYSE on Postgres.

I am an MS SQL user and didn't touch postgres but can I assume analyse is a tool for displaying the chosen query plan for a query?

If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans.

But...probably some tooling could be made to do such analysis automatically and throw similar errors...

Does statistics ever cause query plans to suddenly change on postgres? In MS SQL you would also need to pin the plan / disable statistics on tables...

Re: Speedup of deletes on PostgreSQL

#35

Post by arp242 »

SoftTalker wrote: post #8

This is DBA 101 stuff. If a database is part of your software, you really need someone on the team who knows how it works.

You're not wrong, but unfortunately many teams don't.

Probably my favourite "ya'll don't understand how databases work" was where they "reserved" space for MySQL enums; for example for the "active" column it would be something like:

  enum(
      'active',
      'deleted',
      '_futureval1',
      '_futureval2',
      '_futureval3',
      '_futureval4',
      '_futureval5',
      '_futureval6',
      '_futureval7',
      '_futureval8',
      '_futureval9'
  )
Enums don't work like that at all; it's just a mapping of int to a string value for readability, and you don't need to "reserve" space to add future values just like you don't need to for ints. Adding a new enum value is easy and cheap. Removing them is not as it requires a full scan of all rows to verify they're used. Even worse, you couldn't easily rename enum labels (at the time, don't know if you can now), making it all worse than useless.

Since this was all on large tables and the effort to fix it was relatively large, but without adding much business value, we never fixed it. We were basically stuck with it. It sure as hell annoyed me every time I looked at it.

I'm not an DBA either, but spending about 5 seconds on the documentation for "enum" would have prevented this. This really doesn't require a PhD in SQL.

Re: Speedup of deletes on PostgreSQL

#36

Post by arp242 »

dagss wrote: post #34
John23832 wrote: post #32

Earlier quoted context omitted.

> What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. You can run ANALYSE on Postgres.

I am an MS SQL user and didn't touch postgres but can I assume analyse is a tool for displaying the chosen query plan for a query? If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans. But...probably some tooling could be made to do such analysis…

Analyze collects statistics the query planner uses to determine the query plan. It can change the resulting plan, yes.

Production databases using different query plans sure is annoying and cause problems, but I'm not so sure whether returning errors is better. "Slow" beats "not working at all" in almost all cases. The typical case it will select a different query plan once the data grows, which is not so straight-forward to test for, especially since the hardware of your production may have quite different performance characteristics.

Pinning the plan is temping, but has the downside you risk running a bad plan because what works well for your 100k test rows may not work equally well for your 1b actual rows, and testing all of that is again tricky. That's not really a brilliant either, and may also make your application slow.

Just keeping an eye on slow query logs and/or query performance statistics is the general approach. I don't think it's really possible to improve on that without making some pretty serious trade-offs in other areas.

Re: Speedup of deletes on PostgreSQL

#37

Post by John23832 »

dagss wrote: post #34
John23832 wrote: post #32

Earlier quoted context omitted.

> What popular SQL databases need is an option/hint to return an error instead of taking a slow query plan. You can run ANALYSE on Postgres.

I am an MS SQL user and didn't touch postgres but can I assume analyse is a tool for displaying the chosen query plan for a query? If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans. But...probably some tooling could be made to do such analysis…

> If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans.

The problem with that is what is considered a "poor plan" largely comes down to the indexes used, and suitability of an index is totally dependent on how it is used in the application.

Who sees the error? The DBA? The application developer? What's the cutoff for "poor".

The stats of the query allow those that know/care to make decisions. That's the one size fits all, simple, tool.

Re: Speedup of deletes on PostgreSQL

#38

Post by dagss »

John23832 wrote: post #37
dagss wrote: post #34

Earlier quoted context omitted.

I am an MS SQL user and didn't touch postgres but can I assume analyse is a tool for displaying the chosen query plan for a query? If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans. But...probably some tooling could be made to do such analysis…

> If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans. The problem with that is what is considered a "poor plan" largely comes down to the indexes used, and suitability of an index is totally dependent on how it is used in the application. Who se…

What I had in mind was the simple OLTP usecases. To compete with NoSQL in developer ergonomics.

My context is people choosing NoSQL because SQL is too hard to learn and has too many caveats; not because of performance etc

So basically a mode for the planner where you:

- throw away statistics

- consider all tables infinitely large

- ...except the log(N) of an index lookup is OK

- then find a non-infinite plan or crash

Yes, it does not work in all cases. But it avoids giving SQL a bad reputation and push people towards NoSQL DBs for the common case of simplistic OLTP queries.

The error should be seen during testing. The "DBA" sees it there are issues then you are deploying code without test coverage..

For more sophisticated queries, you would not enable this flag, and be aware that you are doing a nontrivial query.

Re: Speedup of deletes on PostgreSQL

#39

Post by hinkley »

chasil wrote: post #16

Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle. This problem is common. "Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one. "The…

Why aren’t indexes for FK relationships the default?

If you really don’t want one there should be a hint/pragma to turn it off.

It’s just such a stupid reason for a full table scan.

Re: Speedup of deletes on PostgreSQL

#40

Post by dagss »

arp242 wrote: post #36
dagss wrote: post #34

Earlier quoted context omitted.

I am an MS SQL user and didn't touch postgres but can I assume analyse is a tool for displaying the chosen query plan for a query? If so it is in all DBs I would think but it is a bit too manual for my taste, still a big hurdle and opt-in...vs just writing code (including SQL code) and tests like normal and opt-out on getting errors if you get poor plans. But...probably some tooling could be made to do such analysis…

Analyze collects statistics the query planner uses to determine the query plan. It can change the resulting plan, yes. Production databases using different query plans sure is annoying and cause problems, but I'm not so sure whether returning errors is better. "Slow" beats "not working at all" in almost all cases. The typical case it will select a different query plan once the data grows, which is not so straight-for…

Note that I am ONLY talking about a mode to use for limited, trivial OLTP style queries. The kind where the query planner will never be in doubt -- if you just have the right indices in place.

The kind of simple backend software queries where people consider NoSQL instead to avoid SQL's oddities.

The mode I talk about is very inappropriate for any kind of reporting or analytics query or ad hoc queries etc.

> "Slow" beats "not working at all" in almost all cases.

In the specific context specified above, I disagree with this.

Mainly because "not working at all" will be caught during testing (because you should have test coverage of your SQL queries). Slow = undiscovered during testing.

But even assume you didn't have test coverage and the code made it to production -- yes I probably want a seldomly used "DeleteUser" API call to crash if foreign keys were missing indices, instead of doing it anyway and consume lots of DB resources.

> Just keeping an eye on slow query logs and/or query performance statistics is the general approach.

The feature I proposed was to help newbies learn SQL.

This requires expertise in the team, and easily shifts work away from the newbies in the team writing SQL (don't think properly through indices during development) to the single SQL expert in the team.

Depends a bit how your work is organized etc; I like that SQL indices etc are as closely linked to the backend development process as possible; not considered a post-optimization..

If you get the error, you can either make the index you need for a perfect and trivial query plan -- or declare "non-trivial mode" and be back to today's situation.

Post reply on HN