3 ms·

Databases like PostgreSQL don't offer insights into the query plans, does EventReduce parse the SQL statements to determine which tables and rows will be affect

Databases like PostgreSQL don't offer insights into the query plans, does EventReduce parse the SQL statements to determine which tables and rows will be affected by a query and run the appropriate caching or cache invalidation logic?

miohtama 7y ago
It reads like it cannot handle queries over multiple tables.
rustyconover 7y ago
PostgreSQL does offer insights into the plan. Just put EXPLAIN before your query.
- LunaSea 7y ago
  Right but that's when you explicitly ask for it. However, you can't ask ask PostgreSQL to run a query and also return the query plan used for said query.
  - theandrewbailey 7y ago
    Unless there's some weird edge case that I'm not aware of, Postgres will execute what it's planner tells it to. Passing a query to EXPLAIN will show the plan.
    - LunaSea 7y ago
      Query plans are based on database statistics. Explain will calculate the query plan for a query. Explain analyze will calculate the query plan, run the query and compare the query plan expectations to reality. However, if statistics change, so does the query plan. So if you run a query then run the same query again with explain analyze, you don't have a guarantee of getting the same information back. And since explain analyze doesn't return the query results you are obligated to run two separated queries.
      - anarazel 7y ago
        > However, if statistics change, so does the query plan. So if you run a query then run the same query again with explain analyze, you don't have a guarantee of getting the same information back. And since explain analyze doesn't return the query results you are obligated to run two separated queries. If that's a problem you can use auto_explain.
theandrewbailey 7y ago
> Databases like PostgreSQL don't offer insights into the query plans How so? https://www.postgresql.org/docs/current/sql-explain.html https://www.postgresql.org/docs/current/sql-explain.html https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan-in-sql-server https://stackoverflow.com/questions/7359702/how-do-i-obtain-... https://mariadb.com/kb/en/analyze-statement/ https://mariadb.com/kb/en/analyze-statement/ https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_pl...
- LunaSea 7y ago
  This is only for queries where you explicitly want the query plan. However, your query won't return the query results. And regular PostgreSQL queries don't let you return the query plan they used on top the query results.
eventreduce 7y ago
This is a great question. So EventReduce is only the algorithm that calculates your new results. The parsing of SQL is not done by it, you have to bring it by yourself. This works over providing some information about the query like Sort-fields and query-matchers. This is described good in the JavaScript implementation [1]. Providing this functions works easy for NoSQL-Queries because they are better composable. For SQL-queries you have to do some work before you can use EventReduce. Also see the limitations of EventReduce in the readme. [1] https://github.com/pubkey/event-reduce/tree/master/javascript https://github.com/pubkey/event-reduce/tree/master/javascrip...

miohtama 7y ago

It reads like it cannot handle queries over multiple tables.

rustyconover 7y ago

PostgreSQL does offer insights into the plan. Just put EXPLAIN before your query.

LunaSea 7y ago
Right but that's when you explicitly ask for it. However, you can't ask ask PostgreSQL to run a query and also return the query plan used for said query.
- theandrewbailey 7y ago
  Unless there's some weird edge case that I'm not aware of, Postgres will execute what it's planner tells it to. Passing a query to EXPLAIN will show the plan.
  - LunaSea 7y ago
    Query plans are based on database statistics. Explain will calculate the query plan for a query. Explain analyze will calculate the query plan, run the query and compare the query plan expectations to reality. However, if statistics change, so does the query plan. So if you run a query then run the same query again with explain analyze, you don't have a guarantee of getting the same information back. And since explain analyze doesn't return the query results you are obligated to run two separated queries.
    - anarazel 7y ago
      > However, if statistics change, so does the query plan. So if you run a query then run the same query again with explain analyze, you don't have a guarantee of getting the same information back. And since explain analyze doesn't return the query results you are obligated to run two separated queries. If that's a problem you can use auto_explain.

theandrewbailey 7y ago

> Databases like PostgreSQL don't offer insights into the query plans How so? https://www.postgresql.org/docs/current/sql-explain.html https://www.postgresql.org/docs/current/sql-explain.html https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan-in-sql-server https://stackoverflow.com/questions/7359702/how-do-i-obtain-... https://mariadb.com/kb/en/analyze-statement/ https://mariadb.com/kb/en/analyze-statement/ https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_pl...

LunaSea 7y ago
This is only for queries where you explicitly want the query plan. However, your query won't return the query results. And regular PostgreSQL queries don't let you return the query plan they used on top the query results.

eventreduce 7y ago

This is a great question. So EventReduce is only the algorithm that calculates your new results. The parsing of SQL is not done by it, you have to bring it by yourself. This works over providing some information about the query like Sort-fields and query-matchers. This is described good in the JavaScript implementation [1]. Providing this functions works easy for NoSQL-Queries because they are better composable. For SQL-queries you have to do some work before you can use EventReduce. Also see the limitations of EventReduce in the readme. [1] https://github.com/pubkey/event-reduce/tree/master/javascript https://github.com/pubkey/event-reduce/tree/master/javascrip...