Skip to content

Instantly share code, notes, and snippets.

View lfittl's full-sized avatar

Lukas Fittl lfittl

296 followers · 72 following
View GitHub Profile
@lfittl
lfittl / gist:177291
Created August 28, 2009 22:07
attributes | 14 GB | pg_toast_168091 | 168091 | r | 48634464 | 1781955
posts | 6718 MB | pg_toast_168177 | 168177 | r | 24378582 | 859850
posts_for_friendposts | 2676 MB | | 319845 | i | 24378582 | 342577
posts_lastfm_check | 2663 MB | | 319848 | i | 23622196 | 340821
index_attributes_on_post_id_and_type | 2630 MB | | 319833 | i | 48634464 | 336678
posts_for_one_soup | 2608 MB | | 319846 | i | 24378582 | 333769
posts_source | 2478 MB | | 3163105 | i | 24378582 | 317176
posts_guid | 2175
@lfittl
lfittl / gist:177302
Created August 28, 2009 22:35
soup_production=> \d posts
Table "public.posts"
Column | Type | Modifiers
---------------------+--------------------------+----------------------------------------------------
id | bigint | not null default nextval('posts_id_seq'::regclass)
created_at | timestamp with time zone | not null default now()
updated_at | timestamp with time zone |
type | post_type |
deleted | boolean | not null default false
blog_id | bigint |
@lfittl
lfittl / gist:177893
Created August 30, 2009 06:43
02:57:15 automatic analyze of table "soup_production.public.feeds" system usage: CPU 0.02s/2.69u sec
02:59:13 automatic analyze of table "soup_production.public.feeds" system usage: CPU 0.10s/2.68u sec
03:00:11 automatic vacuum of table "soup_production.public.feeds": index scans: 0
pages: 0 removed, 20532 remain
tuples: 0 removed, 62810 remain
system usage: CPU 0.01s/0.00u sec elapsed 3.72 sec
03:00:11 automatic vacuum of table "soup_production.pg_toast.pg_toast_3170179": index scans: 0
pages: 0 removed, 0 remain
tuples: 0 removed, 0 remain
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
@lfittl
lfittl / gist:177918
Created August 30, 2009 10:09
soup_production=# EXPLAIN ANALYZE SELECT posts.id, created_at FROM posts WHERE posts.blog_id = 2 AND LANGUAGE IS NOT NULL AND NOT posts.deleted AND feed_id IS NULL AND TRUE ORDER BY posts.created_at DESC, posts.id DESC LIMIT 40;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=155.67..155.69 rows=37 width=16) (actual time=3747.996..3748.007 rows=40 loops=1)
-> Sort (cost=155.67..155.69 rows=37 width=16) (actual time=3747.994..3747.999 rows=40 loops=1)
Sort Key: created_at, id
Sort Method: top-N heapsort Memory: 26kB
-> Index Scan using posts_lastfm_check on posts (cost=0.00..155.48 rows=37 width=16) (actual time=18.383..3747.373 rows=496 loops=1)
Index Cond: ((feed_id IS NULL) AND (blog_id = 2))
@lfittl
lfittl / 0. BIOS Settings
Created August 31, 2009 10:14
Go to "Advanced => IDE Configuration"
Set "Configure SATA as:" to "AHCI" (was "IDE")
(This is for DL160 G5, it's named differently with the G6. But just remember: We want AHCI, nothing else.)
@lfittl
lfittl / gist:179620
Created September 2, 2009 09:14
Limit (cost=0.00..177216.77 rows=40 width=16)
-> Index Scan Backward using posts_for_friendposts on posts (cost=0.00..418196124.71 rows=94392 width=16)
Filter: ((NOT deleted) AND (blog_id = ANY ('{134368,136333,136043,134837,144282,142198,134939,140991,134222,134158,142307,142012,142154,140746,140586,137319,138615,140870,138400,140353,134166,140437,138984,139962,139804,136704,134196,138305,135312,137821,136401,138154,135297,138358,135028,137449,134756,135892,138312,134105,135529,137476,137521,136381,136005,134700,134288,134263,136394,135117,134938,137192,134630,136807,136787,135505,136589,134209,135420,135010,134165,134541,135575,135987,134243,136687,134220,134225,134219,134267,134283,135877,134951,134955,135315,134163,134237,134320,134890,134119,136390,136406,134128,136332,135851,134162,134112,134124,136379,136359,134109,134156,135989,134239,136229,135278,136393,134327,136403,136342,134136,136395,134968,134134,136405,136188,134922,134101,135030,134104,134576,134121,134157,135216,123374,136106,
@lfittl
lfittl / EXPLAIN ANALYZE
Created September 3, 2009 10:16
Limit (cost=14447.11..14447.21 rows=40 width=16) (actual time=3.157..3.173 rows=40 loops=1)
-> Sort (cost=14447.11..14456.04 rows=3573 width=16) (actual time=3.155..3.163 rows=40 loops=1)
Sort Key: created_at, id
Sort Method: top-N heapsort Memory: 26kB
-> Bitmap Heap Scan on posts (cost=619.31..14334.17 rows=3573 width=16) (actual time=1.325..2.573 rows=776 loops=1)
Recheck Cond: (blog_id = ANY ('{134368,136333,136043,134837,144282,142198,134939,140991,134222,134158,142307,142012,142154,140746,140586,137319,138615,140870,138400,140353,134166,140437,138984,139962,139804,136704,134196,138305,135312,137821,136401,138154,135297,138358,135028,137449,134756,135892,138312,134105,135529,137476,137521,136381,136005,134700,134288,134263,136394,135117,134938,137192,134630,136807,136787,135505,136589,134209,135420,135010,134165,134541,135575,135987,134243,136687,134220,134225,134219,134267,134283,135877,134951,134955,135315,134163,134237,134320,134890,134119,136390,13
@lfittl
lfittl / gist:180403
Created September 3, 2009 17:17
Table "public.posts"
Column | Type | Modifiers
---------------------+--------------------------+----------------------------------------------------
id | bigint | not null default nextval('posts_id_seq'::regclass)
created_at | timestamp with time zone | not null default now()
updated_at | timestamp with time zone |
type | post_type |
deleted | boolean | not null default false
blog_id | bigint |
author_id | integer |
@lfittl
lfittl / friends
Created September 3, 2009 17:35
Limit (cost=0.00..620.06 rows=40 width=16) (actual time=1.187..55.320 rows=40 loops=1)
-> Index Scan Backward using posts_for_friendposts on posts (cost=0.00..2698009.85 rows=174048 width=16) (actual time=1.186..55.305 rows=40 loops=1)
Filter: (blog_id = ANY ('{10,168485,34010,153723,2328,50727,50227,151291,137223,16891,108471,42878,139618,115804,2664,79,122698,129025,119355,122050,27614,7,1,4,8,9,11,12,15,23,24,32,41,47,10470,56,60,67,70,82,1912,2093,2252,2292,4431,7359,13163,13454,17771,27687,28251,35620,11139,28895,22987,30864,864,35835,36914,19306,14356,2367,23299,48,871,27,35725,94511,53870,43202,69,64245,76507,43870,78181,55870,48082,57072,57187,63113,64,9542,36937,46332,56926,60464,8274,22585,9380,55473,60938,28799,37935,33073}'::bigint[]))
Total runtime: 55.364 ms
@lfittl
lfittl / -- join query instead of specifying blog_ids
Created September 3, 2009 17:55
soup_production=> EXPLAIN ANALYZE SELECT posts.id, posts.created_at FROM posts JOIN blogs ON (blogs.id = posts.blog_id) JOIN user_friend ON (blogs.user_id = user_friend.friend_id) WHERE user_friend.user_id = 2 ORDER BY posts.created_at DESC, posts.id DESC LIMIT 40;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2192.72 rows=40 width=16) (actual time=0.584..46.198 rows=40 loops=1)
-> Nested Loop (cost=0.00..6837657.43 rows=124734 width=16) (actual time=0.583..46.186 rows=40 loops=1)
-> Nested Loop (cost=0.00..3748680.73 rows=24974683 width=24) (actual time=0.033..26.752 rows=5245 loops=1)
-> Index Scan Backward using posts_for_friendposts on posts (cost=0.00..2110713.79