MMmmmmmmmmmooooooringin….<\/p>\n
Last night I got the desire to test the database design that I’ve thought out for Velin’s library, and I found it to be slow as hell, so today I’ll be playing a bit with it, to see why. It could change it in the end… And here’s the explain output, for the interested:<\/p>\n
\r\nvelin=> EXPLAIN\r\nvelin-> SELECT b.orig_name, a.name as author, v.path ,g.name as genre\r\nvelin-> FROM \r\nvelin-> books b, authors a, variants v, genres g, relations r, relations r1, relations r2\r\nvelin-> WHERE \r\nvelin-> (b.id=r.id1 AND a.id=r.id2 AND r.rel='BA') AND\r\nvelin-> (b.id=r1.id1 AND v.id=r1.id2 AND r1.rel='BV') AND\r\nvelin-> (b.id=r2.id1 AND g.id=r2.id2 AND r2.rel='BG');\r\n QUERY PLAN \r\n--------------------------------------------------------------------------------------------------------------------\r\n Nested Loop (cost=0.00..22.52 rows=1 width=890)\r\n -> Nested Loop (cost=0.00..17.68 rows=1 width=816)\r\n -> Nested Loop (cost=0.00..12.85 rows=1 width=308)\r\n -> Nested Loop (cost=0.00..8.01 rows=1 width=186)\r\n Join Filter: (\"inner\".id1 = \"outer\".id1)\r\n -> Nested Loop (cost=0.00..6.95 rows=1 width=170)\r\n Join Filter: (\"inner\".id1 = \"outer\".id1)\r\n -> Nested Loop (cost=0.00..5.89 rows=1 width=154)\r\n -> Seq Scan on relations r (cost=0.00..1.05 rows=1 width=16)\r\n Filter: (rel = 'BA'::bpchar)\r\n -> Index Scan using authors_pkey on authors a (cost=0.00..4.82 rows=1 width=154)\r\n Index Cond: (a.id = \"outer\".id2)\r\n -> Seq Scan on relations r2 (cost=0.00..1.05 rows=1 width=16)\r\n Filter: (rel = 'BG'::bpchar)\r\n -> Seq Scan on relations r1 (cost=0.00..1.05 rows=1 width=16)\r\n Filter: (rel = 'BV'::bpchar)\r\n -> Index Scan using books_pkey on books b (cost=0.00..4.82 rows=1 width=154)\r\n Index Cond: (b.id = \"outer\".id1)\r\n -> Index Scan using variants_pkey on variants v (cost=0.00..4.82 rows=1 width=524)\r\n Index Cond: (v.id = \"outer\".id2)\r\n -> Index Scan using genres_pkey on genres g (cost=0.00..4.82 rows=1 width=90)\r\n Index Cond: (g.id = \"outer\".id2)\r\n(22 rows)\r\n<\/pre>\n(the idea is a few tables, and one separate, that describes all the relations. Looks like the postgresql didn’t like it).<\/p>\n
This night I read at last “The moon is a harsh mistress” by Heinlein – in english, printed on about 80 pages (7pt font size, 2 columns). It was worth reading :)<\/p>\n
Today everything seems different – yesterday I cleaned, moved my machine in the big room again (I’ll probably put up some photos later), and now the whole house looks better. There are a few things left to clean in the bathroom, and I’ll probably have to think up a solution for all the empty bottles, but there’s time for everything.<\/p>\n
Something that I found out this morning – I should turn off the bell. To be able to deactivate it when I go to sleep, and activate it when I’m awake. If I was more evil, I whould’ve added an electrical shock for everyone pressing it while I’m asleep…<\/p>\n","protected":false},"excerpt":{"rendered":"
MMmmmmmmmmmooooooringin…. Last night I got the desire to test the database design that I’ve thought out for Velin’s library, and I found it to be slow as hell, so today I’ll be playing a bit with it, to see why. It could change it in the end… And here’s the explain output, for the interested: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-446","post","type-post","status-publish","format-standard","hentry","category-general"],"_links":{"self":[{"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/446","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=446"}],"version-history":[{"count":0,"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=\/wp\/v2\/posts\/446\/revisions"}],"wp:attachment":[{"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=446"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=446"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vasil.ludost.net\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=446"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}