Fork me on GitHub
#sql
<
2020-03-02
>
Ben Hammond14:03:46

if I select a single row from a simple VIEW, does the entire view get built? is there some kind of optimization to only select the viewed data?

parameme16:03:54

In PostgreSQL at least, views (unmaterialised) have no great optimisation applied (statement caching notwithstanding). Think of them as ways of formally sharing SQL (and minorly - providing access control / column order control to existent relations). A WHERE clause on an unmaterialised view will just be folded in as if the view was simply inline in the existent FROM clause. Materialised views calculate and store the results, either en masse or slower but allowing concurrency with other loads on the relation. Until pg 12 common table expressions were always implicitly materialised as well but now you can choose. Basically, to accelerate the results you need to store information (in the tables, indexes and materialised views etc) in a way that allows for the support of your production mix of value selectivity (ratio of rows returned vs total rows) and value change frequency.

👍 4
Ben Hammond14:03:08

> If I enable Performance Insights on my AWS RDS Postgres instance > will it kill the system?

Ben Hammond14:03:23

Is it a thing that can be done safely during the working day?

Ben Hammond14:03:39

Does it need to run out of hours? Anyone out there have an opinion?

parameme16:03:22

Never done that specifically myself but a) you need production SQL, current state of the database, production loads and resultset navigation in order to ascertain real world performance b) there exist ways of getting the necessary statistics and monitoring data quite cheaply in PostgreSQL, surely AWS should have gotten that bit worked out. :man-shrugging:

Ben Hammond17:03:02

Yeah I've concluded that it would probably be fine but would represent an overreach of what I'm supposed to be able to do

Ben Hammond17:03:28

so I'm recommending to the official channels that it would be worthwhile for them to do

👍 4
seancorfield22:03:14

seancorfield/next.jdbc {:mvn/version "1.0.395"} -- https://github.com/seancorfield/next-jdbc/releases/tag/v1.0.395 -- mostly doc improvements since 1.0.384.