| Time |
Nick |
Message |
| 02:41 |
|
Bmagic joined #evergreen |
| 03:00 |
|
scottangel joined #evergreen |
| 03:00 |
|
Bmagic joined #evergreen |
| 03:00 |
|
dluch joined #evergreen |
| 03:00 |
|
Jaysal joined #evergreen |
| 03:09 |
|
Jaysal joined #evergreen |
| 03:11 |
|
scottangel joined #evergreen |
| 03:12 |
|
Bmagic joined #evergreen |
| 03:12 |
|
dluch joined #evergreen |
| 07:47 |
|
collum joined #evergreen |
| 08:30 |
|
mmorgan joined #evergreen |
| 10:27 |
|
berick joined #evergreen |
| 10:35 |
|
redavis joined #evergreen |
| 10:56 |
|
Dyrcona joined #evergreen |
| 11:05 |
Dyrcona |
Whatup, Chrome? Why you bogging my system down? |
| 11:11 |
redavis |
Chrome gotta be boggy. It's a core part of its personality. |
| 11:38 |
|
jihpringle joined #evergreen |
| 11:50 |
* Dyrcona |
has been learning some interesting things about views and performance in PostgreSQL. For instance, selecting 1 from a traditional view or materialized view with the same definition takes about the same amount of time, give or take a couple milliseconds. |
| 11:51 |
Dyrcona |
However, selecting all of the 1.75 million rows takes 1/3 the time with the materialized view as it does with the traditional view in my situation. |
| 11:52 |
redavis |
That's what one would hope to experience, right? The materialized view is supposed to be more efficient? |
| 11:52 |
Bmagic |
a materialized view is a table, whereas a view is a query wrapped up to look like a table (right?) |
| 11:52 |
redavis |
lol, Bmagic++ |
| 11:53 |
Dyrcona |
Basically, yes. But a materialized view isn't quite a table. Cool thing with a real materialized view is you can refresh it when you want to update the data. |
| 11:53 |
Dyrcona |
I imagine the mat view would be faster in joins, too. |
| 11:53 |
Bmagic |
I would imagine a view would be slower than a raw table, because the view is just another query, which enguages the query planner at a deeper level |
| 11:54 |
Dyrcona |
I find it interesting that grabbing only 1 row takes about the same time with either, though. |
| 11:55 |
Bmagic |
hmm, I'm not sure how retrieving a single row is much of a test |
| 11:55 |
Dyrcona |
Also, I've written queries with subqueries that are faster than those with joins on tables, and a view is a query more or less, so I guess it depends. |
| 11:55 |
Dyrcona |
Bmagic: It's not really, but I was curious about various timings. |
| 11:56 |
Dyrcona |
This goes back to something that I mentioned yesterday about a custom view we have that got really slow with Pg 16 and the slowness not being where I expected it to be. |
| 11:56 |
Bmagic |
if your query that retrieves a single row is using a filter such as "id=1", and assuming id is indexed, the timing should be the same between the two? |
| 11:56 |
Dyrcona |
I decided to experiment a little with a materialized view this morning. |
| 11:57 |
Dyrcona |
Well, the traditional view can't be indexed. |
| 11:57 |
Bmagic |
that's where the table prevails: indexes? |
| 11:57 |
Dyrcona |
The mat view does have a unique index on the id column (bib_record) in this case. |
| 11:58 |
Dyrcona |
Well, that's the thing. It's not looking like the index makes much difference. There are a number of situations (including tables with small rows) where PostgreSQL will ignore the index and do a scan. |
| 11:59 |
Dyrcona |
This view has only 2 columns. |
| 11:59 |
Bmagic |
I like what you're doing |
| 11:59 |
Bmagic |
science! |
| 11:59 |
Dyrcona |
I can drop the index and see, but I don't think it will matter. |
| 11:59 |
Dyrcona |
Yeah, good ol' trial and error. :) |
| 12:00 |
Bmagic |
that's the main way I've ever been able to extract wisdom |
| 12:00 |
Dyrcona |
Same. |
| 12:01 |
Dyrcona |
Think I'll recreate the traditional view with a more efficient (for recent PG) implementation in production and create the materialized view on our dev system so we can experiment with it some more. |
| 12:01 |
|
jihpringle69 joined #evergreen |
| 12:03 |
Bmagic |
a mat view is a table that is kept in sync via trigger(s), or maybe I don't understand |
| 12:03 |
Dyrcona |
One and 3/4 million rows seems like a lot for a regular view. |
| 12:04 |
Dyrcona |
That's what "we" call a materialized view, but not what Postgres considers a materialized view: https://www.postgresql.org/docs/16/sql-creatematerializedview.html |
| 12:04 |
Dyrcona |
It's an actual thing. |
| 12:07 |
Bmagic |
ok, got it |
| 12:09 |
Bmagic |
so the mat view, when created "WITH DATA" - is seeded with the results of the [query], and stays static until "REFRESH MATERIALIZED VIEW" is executed? |
| 12:12 |
Dyrcona |
Yes. |
| 12:13 |
Dyrcona |
So, I dropped the index on the materialized view, and it take 80 milliseconds longer to retrieve a single row. This makes getting a single row from the materialized view slower than getting that same row from the traditional view. |
| 12:14 |
Dyrcona |
It's pretty consistent over 10 runs. (I'm not automating thousands of iterations and averaging them out.) |
| 12:14 |
Dyrcona |
"WITH DATA" is the default. |
| 12:26 |
* Dyrcona |
considers cloning the PostgreSQL git repo. I imagine that I could learn a lot by reading the code. |
| 12:30 |
eeevil |
FWIW, real materialized views were not a feature when we started EG, they weren't efficient to rebuild until (relatively) recently, and they have to be manually refreshed. thus our "traditional" trigger-based ones. (1) is solved (obv), (2) is partly solved (see: CONCURRENTLY), but (3) is not, so there's no "real mat-view" that stays up to date |
| 12:32 |
Dyrcona |
eeevil: Yep. |
| 12:33 |
redavis |
To be fair, nothing we perceive is actually up-to-date. |
| 12:33 |
Dyrcona |
I was considering the trigger approach but decided that asset.copy has enough triggers, rules, and fk relationships already. It doesn't need another. |
| 12:34 |
Dyrcona |
We're going with the traditional view with an improved query for Pg 16 in production. I'll see what folks here say about putting the materialized view on a dev system for testing. I'd like to have the person who uses it test both with their usual workflows. |
| 12:35 |
eeevil |
redavis: tell that to circ managers running reports! ;) |
| 12:35 |
redavis |
eeevil, do they really want to hear about the mythology of "instant" though? |
| 12:36 |
eeevil |
whether they want to or not... |
| 12:36 |
redavis |
And, do I want to live in a perpetual state of STFU as a response to their consternation? |
| 12:36 |
redavis |
(do I already live in that state?) |
| 12:36 |
redavis |
(what is reality?) |
| 12:37 |
redavis |
Sorry. I read the news today. And got a papercut (unrelated). Humaning is difficult now. |
| 12:37 |
redavis |
Humanning? Peopling. |
| 12:37 |
redavis |
Being around people. |
| 12:37 |
Dyrcona |
If being human were easy, we would not have literature or any other art. |
| 12:38 |
eeevil |
but at least Canada is ok for the moment! |
| 12:38 |
* eeevil |
runs to lunch |
| 12:38 |
redavis |
lol |
| 12:38 |
redavis |
Canada! |
| 12:39 |
Dyrcona |
eeevil: Some other considerations that I have with this particular view and triggers on acp include just how often is the view used, and how up to date does it need to be? Is the accuracy worth potentially adding another 200ms (just a number I picked because it "feels right') to every copy update worth it? |
| 12:41 |
Dyrcona |
If we can refresh the mat view once every hour, 1/2 hour, 15minutes, whatever, and it takes seconds, is that good enough? In my estimation, probably yes, and I don't slow down copy updates that happen far more frequently. |
| 12:41 |
Dyrcona |
And I'll grant you that performance in Pg 8.3 was nothing like it is in Pg 16. |
| 12:43 |
Dyrcona |
In reality it will probably add less than 100ms to a copy update, maybe if the trigger is deferred and after and something else, it won't really add anything at all. I dunno if I can have a trigger not block a transaction from committing. I'll have to look. |
| 12:44 |
Dyrcona |
Anyway, that was where my train of thought was heading with the decision to test out a materialized view. |
| 12:45 |
Dyrcona |
I find it interesting that the mat view without the index is slower for individual record retrieval than the traditional view in my non-scientific test. It does seem faster to use overall, though. |
| 12:48 |
Dyrcona |
There's some viewdoo going on... *coughs* |
| 12:54 |
redavis |
Dyrcona, I think it's cool that you're messing with optimizations. |
| 12:56 |
Dyrcona |
redavis: Thank you. This is a CWMARS view that was implemented before I got here. It was never really fast because 1.5 to 1.75 million rows are not fast in a view. When we upgraded to Pg 16, it got really slow, so I had a look with explain and started changing it. The performance of the traditional view is back to where it was pre Pg 16. I want to see if the materialized view is even better. |
| 12:57 |
redavis |
Dyrcona++ |
| 13:01 |
|
jihpringle joined #evergreen |
| 13:02 |
Dyrcona |
I always find it interesting to look into what actually make my queries slow, It often is not the part that I thought. |
| 13:05 |
berick |
gotta shake that viewdoo right off your shoes |
| 13:05 |
redavis |
Dyrcona, it's good that you have the wherewithal to track such things down. |
| 13:05 |
redavis |
berick++ |
| 13:06 |
Dyrcona |
berick++ |
| 13:12 |
Dyrcona |
Ugh. Inverted my branch names in a rebase, now I have to reset --hard and do it over. |
| 13:16 |
redavis |
While working on documentation stuff, it's always both infuriating and heartwarming to know that ALL software has issues. |
| 13:41 |
|
jihpringle joined #evergreen |
| 14:04 |
sleary |
the Launchpad Itself project has something like 5000+ open bugs... and no tag for accessibility, even though (among other things) not a single one of their form labels is properly connected to its input >.< |
| 14:05 |
redavis |
sleary, I want to confirm that I've read what you've posted and have nothing novel or appropriate to respond with (other than this sentence with a dangling participle). You are a good and brave human for looking into that. |
| 14:06 |
sleary |
hehehehe |
| 14:12 |
redavis |
Hmm, I think prior to reviewing all the configuration and workflows for fund administration, lunch is probably a good idea. This is going to be a lot. |
| 15:03 |
|
jihpringle93 joined #evergreen |
| 15:04 |
Dyrcona |
Back to my materialized view versus traditional view: Aggregates on the count column are a lot faster with the materialized view. |
| 16:49 |
|
jihpringle joined #evergreen |
| 17:17 |
|
mmorgan left #evergreen |
| 17:23 |
|
jihpringle joined #evergreen |
| 17:44 |
|
jihpringle joined #evergreen |
| 18:00 |
|
jihpringle joined #evergreen |
| 19:15 |
|
jihpringle joined #evergreen |
