No title

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
ircbrowse=> explain analyze select * from event where channel = 1
order by id offset 500000 limit 30;

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2825611.65..2825781.19 rows=30 width=85) (actual
time=139.683..139.695 rows=30 loops=1)
   ->  Index Scan using event_unique_id on event
(cost=0.00..83313035.23 rows=14742478 width=85) (actual
time=0.030..126.872 rows=500030 loops=1)
         Filter: (channel = 1)
 Total runtime: 139.715 ms
(4 rows)

ircbrowse=> \timing
Timing is on.
ircbrowse=> create index on event(channel,id);
CREATE INDEX
Time: 63629.808 ms
ircbrowse=> explain analyze select * from event where channel = 1 order by id offset 500000 limit 30;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1853988.73..1854099.97 rows=30 width=85) (actual
time=134.681..134.698 rows=30 loops=1)
   ->  Index Scan using event_channel_id_idx on event
(cost=0.00..72254560.84 rows=19486246 width=85) (actual
time=0.025..121.692 rows=500030 loops=1)
         Index Cond: (channel = 1)
 Total runtime: 134.719 ms
(4 rows)

Time: 135.287 ms
ircbrowse=> analyze;
WARNING:  skipping "pg_authid" --- only superuser can analyze it
WARNING:  skipping "pg_database" --- only superuser can analyze it
WARNING:  skipping "pg_tablespace" --- only superuser can analyze it
WARNING:  skipping "pg_pltemplate" --- only superuser can analyze it
WARNING:  skipping "pg_auth_members" --- only superuser can analyze it
WARNING:  skipping "pg_shdepend" --- only superuser can analyze it
WARNING:  skipping "pg_shdescription" --- only superuser can analyze it
WARNING:  skipping "pg_db_role_setting" --- only superuser can analyze it
ANALYZE
Time: 2113.811 ms
ircbrowse=> select * from event where channel = 1 order by id offset 500000 limit 30;
Time: 110.252 ms
ircbrowse=> select * from event where channel = 1 order by id offset 1900000 limit 30;
Time: 47073.852 ms
ircbrowse=>