татко Крокодил

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:

velin=> EXPLAIN
velin-> SELECT b.orig_name, a.name as author, v.path ,g.name as genre
velin->         FROM 
velin->                  books b, authors a, variants v, genres g, relations r, relations r1, relations r2
velin->         WHERE 
velin->                 (b.id=r.id1 AND a.id=r.id2 AND r.rel='BA') AND
velin->                 (b.id=r1.id1 AND v.id=r1.id2 AND r1.rel='BV') AND
velin->                 (b.id=r2.id1 AND g.id=r2.id2 AND r2.rel='BG');
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..22.52 rows=1 width=890)
   ->  Nested Loop  (cost=0.00..17.68 rows=1 width=816)
         ->  Nested Loop  (cost=0.00..12.85 rows=1 width=308)
               ->  Nested Loop  (cost=0.00..8.01 rows=1 width=186)
                     Join Filter: ("inner".id1 = "outer".id1)
                     ->  Nested Loop  (cost=0.00..6.95 rows=1 width=170)
                           Join Filter: ("inner".id1 = "outer".id1)
                           ->  Nested Loop  (cost=0.00..5.89 rows=1 width=154)
                                 ->  Seq Scan on relations r  (cost=0.00..1.05 rows=1 width=16)
                                       Filter: (rel = 'BA'::bpchar)
                                 ->  Index Scan using authors_pkey on authors a  (cost=0.00..4.82 rows=1 width=154)
                                       Index Cond: (a.id = "outer".id2)
                           ->  Seq Scan on relations r2  (cost=0.00..1.05 rows=1 width=16)
                                 Filter: (rel = 'BG'::bpchar)
                     ->  Seq Scan on relations r1  (cost=0.00..1.05 rows=1 width=16)
                           Filter: (rel = 'BV'::bpchar)
               ->  Index Scan using books_pkey on books b  (cost=0.00..4.82 rows=1 width=154)
                     Index Cond: (b.id = "outer".id1)
         ->  Index Scan using variants_pkey on variants v  (cost=0.00..4.82 rows=1 width=524)
               Index Cond: (v.id = "outer".id2)
   ->  Index Scan using genres_pkey on genres g  (cost=0.00..4.82 rows=1 width=90)
         Index Cond: (g.id = "outer".id2)
(22 rows)

(the idea is a few tables, and one separate, that describes all the relations. Looks like the postgresql didn’t like it).

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 :)

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.

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…

This entry was posted on Monday, August 2nd, 2004 at 13:57 and is filed under General. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.