little-arhat/01_schema.sql Secret

## 01_schema.sql
create table timeline_entries (
  id serial not null,
  created_at timestamp NOT NULL,
  timeline_id integer,
  post_id integer,
  fresh_at timestamp,
  constraint timeline_entries__pk PRIMARY KEY(id));

create index t4 on timeline_entries (fresh_at DESC, timeline_id);

## 02_populate.sql
insert into timeline_entries (created_at, timeline_id, post_id, fresh_at) (select
    timestamp '2015-01-01 00:00:00' + random() * (timestamp '2015-10-20 20:00:00' - timestamp '2015-01-01 00:00:00') as created_at,
    trunc(random() * 13000 + 1) as timeline_id,
    trunc(random() * 40000 + 1) as post_id,
    timestamp '2015-01-01 00:00:00' + random() * (timestamp '2015-10-20 20:00:00' - timestamp '2015-01-01 00:00:00') as fresh_at
from generate_series(1, 468500));

## 03_test
postgres=# explain analyze SELECT id
FROM timeline_entries WHERE timeline_id IN (select trunc(random() * 13000 + 1) from generate_series(1, 160)) ORDER BY fresh_at DESC limit 100;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..2080.59 rows=100 width=12) (actual time=2.832..372.836 rows=100 loops=1)
   ->  Nested Loop Semi Join  (cost=0.42..4872786.05 rows=234250 width=12) (actual time=2.831..372.812 rows=100 loops=1)
         Join Filter: ((timeline_entries.timeline_id)::double precision = (trunc(((random() * 13000::double precision) + 1::double precision))))
         Rows Removed by Join Filter: 1243789
         ->  Index Scan using t4 on timeline_entries  (cost=0.42..28035.60 rows=468500 width=16) (actual time=0.268..79.320 rows=7831 loops=1)
         ->  Materialize  (cost=0.00..35.00 rows=1000 width=8) (actual time=0.000..0.012 rows=159 loops=7831)
               ->  Function Scan on generate_series  (cost=0.00..20.00 rows=1000 width=0) (actual time=0.124..0.214 rows=160 loops=1)
 Planning time: 0.745 ms
 Execution time: 373.083 ms
(9 rows)


postgres=# explain analyze SELECT id
FROM timeline_entries WHERE timeline_id IN (11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978) ORDER BY fresh_at DESC limit 100;
                                                                                                                                                                                                                                                                                                                                                                                                                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..2115.35 rows=100 width=12) (actual time=0.032..16.188 rows=100 loops=1)
   ->  Index Scan using t4 on timeline_entries  (cost=0.42..121735.60 rows=5756 width=12) (actual time=0.031..16.164 rows=100 loops=1)
         Filter: (timeline_id = ANY ('{11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978}'::integer[]))
         Rows Removed by Filter: 6269
 Planning time: 1.169 ms
 Execution time: 16.283 ms
(6 rows)

## 04_test2
postgres=# drop index t4;
DROP INDEX
postgres=# create index t4 on timeline_entries (timeline_id, fresh_at DESC);
CREATE INDEX

postgres=# explain analyze SELECT id
FROM timeline_entries WHERE timeline_id IN (11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978) ORDER BY fresh_at DESC limit 100;
                                                                                                                                                                                                                                                                                                                                                                                                                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=5724.73..5724.98 rows=100 width=12) (actual time=8.762..8.791 rows=100 loops=1)
   ->  Sort  (cost=5724.73..5739.12 rows=5756 width=12) (actual time=8.760..8.772 rows=100 loops=1)
         Sort Key: fresh_at
         Sort Method: top-N heapsort  Memory: 29kB
         ->  Bitmap Heap Scan on timeline_entries  (cost=728.44..5504.74 rows=5756 width=12) (actual time=2.529..7.334 rows=5738 loops=1)
               Recheck Cond: (timeline_id = ANY ('{11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978}'::integer[]))
               Heap Blocks: exact=2758
               ->  Bitmap Index Scan on t4  (cost=0.00..727.00 rows=5756 width=0) (actual time=1.933..1.933 rows=5738 loops=1)
                     Index Cond: (timeline_id = ANY ('{11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978}'::integer[]))
 Planning time: 0.793 ms
 Execution time: 8.882 ms
(11 rows)
	create table timeline_entries (
	id serial not null,
	created_at timestamp NOT NULL,
	timeline_id integer,
	post_id integer,
	fresh_at timestamp,
	constraint timeline_entries__pk PRIMARY KEY(id));

	create index t4 on timeline_entries (fresh_at DESC, timeline_id);
	insert into timeline_entries (created_at, timeline_id, post_id, fresh_at) (select
	timestamp '2015-01-01 00:00:00' + random() * (timestamp '2015-10-20 20:00:00' - timestamp '2015-01-01 00:00:00') as created_at,
	trunc(random() * 13000 + 1) as timeline_id,
	trunc(random() * 40000 + 1) as post_id,
	timestamp '2015-01-01 00:00:00' + random() * (timestamp '2015-10-20 20:00:00' - timestamp '2015-01-01 00:00:00') as fresh_at
	from generate_series(1, 468500));
	postgres=# explain analyze SELECT id
	FROM timeline_entries WHERE timeline_id IN (select trunc(random() * 13000 + 1) from generate_series(1, 160)) ORDER BY fresh_at DESC limit 100;
	QUERY PLAN
	-------------------------------------------------------------------------------------------------------------------------------------------------
	Limit (cost=0.42..2080.59 rows=100 width=12) (actual time=2.832..372.836 rows=100 loops=1)
	-> Nested Loop Semi Join (cost=0.42..4872786.05 rows=234250 width=12) (actual time=2.831..372.812 rows=100 loops=1)
	Join Filter: ((timeline_entries.timeline_id)::double precision = (trunc(((random() * 13000::double precision) + 1::double precision))))
	Rows Removed by Join Filter: 1243789
	-> Index Scan using t4 on timeline_entries (cost=0.42..28035.60 rows=468500 width=16) (actual time=0.268..79.320 rows=7831 loops=1)
	-> Materialize (cost=0.00..35.00 rows=1000 width=8) (actual time=0.000..0.012 rows=159 loops=7831)
	-> Function Scan on generate_series (cost=0.00..20.00 rows=1000 width=0) (actual time=0.124..0.214 rows=160 loops=1)
	Planning time: 0.745 ms
	Execution time: 373.083 ms
	(9 rows)


	postgres=# explain analyze SELECT id
	FROM timeline_entries WHERE timeline_id IN (11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978) ORDER BY fresh_at DESC limit 100;
	QUERY PLAN
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	Limit (cost=0.42..2115.35 rows=100 width=12) (actual time=0.032..16.188 rows=100 loops=1)
	-> Index Scan using t4 on timeline_entries (cost=0.42..121735.60 rows=5756 width=12) (actual time=0.031..16.164 rows=100 loops=1)
	Filter: (timeline_id = ANY ('{11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978}'::integer[]))
	Rows Removed by Filter: 6269
	Planning time: 1.169 ms
	Execution time: 16.283 ms
	(6 rows)
	postgres=# drop index t4;
	DROP INDEX
	postgres=# create index t4 on timeline_entries (timeline_id, fresh_at DESC);
	CREATE INDEX

	postgres=# explain analyze SELECT id
	FROM timeline_entries WHERE timeline_id IN (11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978) ORDER BY fresh_at DESC limit 100;
	QUERY PLAN
	------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	Limit (cost=5724.73..5724.98 rows=100 width=12) (actual time=8.762..8.791 rows=100 loops=1)
	-> Sort (cost=5724.73..5739.12 rows=5756 width=12) (actual time=8.760..8.772 rows=100 loops=1)
	Sort Key: fresh_at
	Sort Method: top-N heapsort Memory: 29kB
	-> Bitmap Heap Scan on timeline_entries (cost=728.44..5504.74 rows=5756 width=12) (actual time=2.529..7.334 rows=5738 loops=1)
	Recheck Cond: (timeline_id = ANY ('{11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978}'::integer[]))
	Heap Blocks: exact=2758
	-> Bitmap Index Scan on t4 (cost=0.00..727.00 rows=5756 width=0) (actual time=1.933..1.933 rows=5738 loops=1)
	Index Cond: (timeline_id = ANY ('{11304,12263,6501,6376,5010,9763,11213,9165,5500,10209,1107,3330,10479,9306,12480,7616,8880,2436,11310,11531,10212,9613,10163,11076,9909,7318,6875,8252,12813,3418,4922,11116,2680,11423,4492,7690,8186,2704,3855,685,12913,4961,4014,10392,1266,3493,5008,10146,5929,3317,8677,3141,12930,5840,1217,9839,157,8091,5090,12969,11509,10012,11085,1188,8435,2576,8878,3620,5280,12732,4304,5193,4693,8318,2584,5959,11810,7591,3105,4739,10908,11781,7879,10837,4621,9096,7675,4777,4186,12765,4746,2694,9777,2831,3882,5211,5407,12760,8830,10687,12491,134,2879,4184,8451,5462,10142,7261,53,246,11999,10960,12027,6878,8797,3647,2973,3472,8424,7158,3236,170,9852,13,3001,733,5223,8407,492,1053,6094,12983,1186,8972,4166,9636,1434,1308,3896,1486,1554,2895,12446,580,9772,8242,4227,12744,11713,12651,6902,1949,12821,3753,1961,2821,4486,7184,11228,4978}'::integer[]))
	Planning time: 0.793 ms
	Execution time: 8.882 ms
	(11 rows)