4 ms·

Very interesting. I never really "got" declarative languages, I remember a very long time ago I was working with Oracle and you could see the "execution plan" f

Very interesting. I never really "got" declarative languages, I remember a very long time ago I was working with Oracle and you could see the "execution plan" for your SQL queries. I kept wondering "why can't I build my queries directly with this?" - it seems so much simpler to my brain than SQL itself.

TurkTurkleton 7y ago
You can't write query plans directly because you would have to manually take into account a number of factors that the query planner considers for you automatically, such as size of the table, statistics on the distribution of values, whether there are indexes that could be used to speed the query, and so on. Some SQL dialects (like Microsoft's T-SQL) do give you some ability to influence the decisions the query planner makes, though, like forcing it to use specific indexes, or forcing it to use scans or seeks.
- marcosdumay 7y ago
  And then comes Oracle and insists on applying equality filters first, because, duh, they are fast, and never take any of those other details into consideration. Honestly, Postgres does it right - you can enforce your query plan to any level of detail you want.
  - paulryanrogers 7y ago
    Does PostgreSQL have plan hints now? I thought they were opposed to them for fear they become unmaintainable and hard to read.
    - lfittl 7y ago
      If you really want to, the pg_hint_plan extension can be used for this - though I would use it very sparingly, if at all: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html (available on some cloud providers as well)
  - yen223 7y ago
    How do you do that? Postgres doesn't even offer a way to enforce that specific indexes get used during a query as far as I know.
- perl4ever 7y ago
  As a practical matter, when you can't control the optimizer or affect how the DBAs configure things, you break your huge query into multiple ones with temporary tables. This is from the perspective of trying to get queries to run in 5 minutes instead of 30 minutes instead of hours or days or forever, not brief transactions measured in milliseconds. And it's not something I figured out on my own, but by paying attention to the guy who never talked but was consistently 10x faster in producing reports than anyone. The thing you should not do, that I also saw people do, is use procedural PL/SQL or T-SQL to process things in a loop - that can be orders of magnitude slower.
greggyb 7y ago
You can. First, find or implement a library in your language of choice to store indexed data on disk. Then implement some very hairy logic to make sure your read and write operations are ACID. Then choose an algorithm for the query you'd like to write. And then write it. I don't intend to be glib, but rather to illustrate some of the benefits of a database platform like modern RDBMSes. These things are all implemented and battle-tested for you. If you would prefer a more fine-grained control over execution of queries, you could probably get very far by starting with the SQLite code base and working at a more primitive level through its library.
eyelidlessness 7y ago
> I never really "got" declarative languages As I responded to another comment, I have to wonder if that's because you haven't spent the time to become proficient enough to appreciate them. I understand that a lot of programmers decry "magic" and want to get at the underlying steps and components that produce a given result, but literally all of us work at some level of abstraction above that because it would be impossible not to. None of us are directly transforming sand with electricity into computed values. You're describing being more comfortable with an imperative set of statements than a declarative description of results. Assuming you write automated tests, think of the declarative approach as writing the description (rather than the implementation) of a test, except that the description is less freeform and must conform to a certain syntax and structure so that a machine can write the test for you. It's "magic", but it's only so much magic because the scope of what it can do for you is limited and well defined.
- wruza 7y ago
  Assuming you write automated tests, think of the declarative approach as writing the description (rather than the implementation) of a test Except that you know/expect how exactly it will be executed and if it doesn’t, maybe test should not really pass. SQL in general is full of this “we write declarative queries expecting this exact imperative result and investigating if it’s not”. It’s much like an interview question: it may have many different answers, but you must provide the one that satisfies a grumpy plan builder guy. I know sql and use it when it’s shorter/more descriptive, but sometimes you just want to take them rows and do them things, without leaving a database process and its guarantees. Not much db experience, but the fact that such powerful engines (i.e. acid, indexing, good ods, pooling, etc) are always hidden beyond some cryptic uppercase frontend with a weak execution layer always bothered me. Just give me that postgres-grade index scan directly in C, dammit. /rant (inb4 just write some parts as a sp in a language of choice)
  - eyelidlessness 7y ago
    > Except that you know/expect how exactly it will be executed You're writing bad tests. Do yourself a favor and write tests that pass "it [satisfies some human expectation]". Stop writing tests that care about how it's implemented.
keeperofdakeys 7y ago
Data changes, so the best query plan changes with it. SQL was built to handle this data-dependent environment. Is one column composed of mostly one or two values? Then an index lookup on that column is not very optimal, and the database can use something else. There is more than one type of join (INNER, LEFT OUTER, etc), and more than one join algorithm (neesed loop, merge, hash, etc). All these change based on the data. Even the join order can have a huge impact on query time, and needs to adapt based on the number of rows you'll pull from each table. A lot of SQL queries are built from templates, or built by ORMs. Optimisations are critical to turn these templates queries into something efficient. SQL can also be very expressive, a "NOT EXISTS (SELECT 1 FROM thing WHERE foobar)" could be more readable than doing a join and where clause. Though interestingly, you get a much more declarative query style with nosql databases and key-value stores. So there are alternatives out there.

TurkTurkleton 7y ago

You can't write query plans directly because you would have to manually take into account a number of factors that the query planner considers for you automatically, such as size of the table, statistics on the distribution of values, whether there are indexes that could be used to speed the query, and so on. Some SQL dialects (like Microsoft's T-SQL) do give you some ability to influence the decisions the query planner makes, though, like forcing it to use specific indexes, or forcing it to use scans or seeks.

marcosdumay 7y ago
And then comes Oracle and insists on applying equality filters first, because, duh, they are fast, and never take any of those other details into consideration. Honestly, Postgres does it right - you can enforce your query plan to any level of detail you want.
- paulryanrogers 7y ago
  Does PostgreSQL have plan hints now? I thought they were opposed to them for fear they become unmaintainable and hard to read.
  - lfittl 7y ago
    If you really want to, the pg_hint_plan extension can be used for this - though I would use it very sparingly, if at all: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html (available on some cloud providers as well)
- yen223 7y ago
  How do you do that? Postgres doesn't even offer a way to enforce that specific indexes get used during a query as far as I know.
perl4ever 7y ago
As a practical matter, when you can't control the optimizer or affect how the DBAs configure things, you break your huge query into multiple ones with temporary tables. This is from the perspective of trying to get queries to run in 5 minutes instead of 30 minutes instead of hours or days or forever, not brief transactions measured in milliseconds. And it's not something I figured out on my own, but by paying attention to the guy who never talked but was consistently 10x faster in producing reports than anyone. The thing you should not do, that I also saw people do, is use procedural PL/SQL or T-SQL to process things in a loop - that can be orders of magnitude slower.

greggyb 7y ago

You can. First, find or implement a library in your language of choice to store indexed data on disk. Then implement some very hairy logic to make sure your read and write operations are ACID. Then choose an algorithm for the query you'd like to write. And then write it. I don't intend to be glib, but rather to illustrate some of the benefits of a database platform like modern RDBMSes. These things are all implemented and battle-tested for you. If you would prefer a more fine-grained control over execution of queries, you could probably get very far by starting with the SQLite code base and working at a more primitive level through its library.

eyelidlessness 7y ago

> I never really "got" declarative languages As I responded to another comment, I have to wonder if that's because you haven't spent the time to become proficient enough to appreciate them. I understand that a lot of programmers decry "magic" and want to get at the underlying steps and components that produce a given result, but literally all of us work at some level of abstraction above that because it would be impossible not to. None of us are directly transforming sand with electricity into computed values. You're describing being more comfortable with an imperative set of statements than a declarative description of results. Assuming you write automated tests, think of the declarative approach as writing the description (rather than the implementation) of a test, except that the description is less freeform and must conform to a certain syntax and structure so that a machine can write the test for you. It's "magic", but it's only so much magic because the scope of what it can do for you is limited and well defined.

wruza 7y ago
Assuming you write automated tests, think of the declarative approach as writing the description (rather than the implementation) of a test Except that you know/expect how exactly it will be executed and if it doesn’t, maybe test should not really pass. SQL in general is full of this “we write declarative queries expecting this exact imperative result and investigating if it’s not”. It’s much like an interview question: it may have many different answers, but you must provide the one that satisfies a grumpy plan builder guy. I know sql and use it when it’s shorter/more descriptive, but sometimes you just want to take them rows and do them things, without leaving a database process and its guarantees. Not much db experience, but the fact that such powerful engines (i.e. acid, indexing, good ods, pooling, etc) are always hidden beyond some cryptic uppercase frontend with a weak execution layer always bothered me. Just give me that postgres-grade index scan directly in C, dammit. /rant (inb4 just write some parts as a sp in a language of choice)
- eyelidlessness 7y ago
  > Except that you know/expect how exactly it will be executed You're writing bad tests. Do yourself a favor and write tests that pass "it [satisfies some human expectation]". Stop writing tests that care about how it's implemented.

keeperofdakeys 7y ago

Data changes, so the best query plan changes with it. SQL was built to handle this data-dependent environment. Is one column composed of mostly one or two values? Then an index lookup on that column is not very optimal, and the database can use something else. There is more than one type of join (INNER, LEFT OUTER, etc), and more than one join algorithm (neesed loop, merge, hash, etc). All these change based on the data. Even the join order can have a huge impact on query time, and needs to adapt based on the number of rows you'll pull from each table. A lot of SQL queries are built from templates, or built by ORMs. Optimisations are critical to turn these templates queries into something efficient. SQL can also be very expressive, a "NOT EXISTS (SELECT 1 FROM thing WHERE foobar)" could be more readable than doing a join and where clause. Though interestingly, you get a much more declarative query style with nosql databases and key-value stores. So there are alternatives out there.