SELECT post, thread, reply, sub FROM board WHERE thread=45718 ORDER BY 2,3; post | thread | reply | sub --------+--------+-------+-------------------------------------------------- 204905 | 45718 | -8 | Have fun stormin the castle! 204906 | 45718 | 1 | Re: Have fun stormin the castle! 204907 | 45718 | 2 | Re: Have fun stormin the castle! 204908 | 45718 | 3 | Re: Have fun stormin the castle! 204909 | 45718 | 4 | Re: Have fun stormin the castle! 204910 | 45718 | 5 | Re: Have fun stormin the castle! (think it'll work?) 204911 | 45718 | 6 | Re: Have fun stormin the castle! (think it'll work?) 204912 | 45718 | 7 | Re: Have fun stormin the castle! 204913 | 45718 | 8 | Re: Have fun stormin the castle!
Table "public.board"
Column | Type | Modifiers
----------+--------------------------+------------------------
post | bigint | not null
thread | integer | not null
reply | smallint | not null
sub | character varying(80) |
stat | smallint | not null
book | text |
cdate | timestamp with time zone | not null default now()
postedby | integer | not null default 0
Previous thread?
EXPLAIN ANALYZE SELECT post, thread, sub FROM board WHERE thread < 45718 AND reply<1 AND stat=1 ORDER BY thread DESC LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------
Limit (C=27.51..27.52 R=1) (A=45036.83..45036.85 1/1)
-> Sort (C=27.51..27.52 R=1) (A=45036.81..45036.82 2/1)
Sort Key: thread
-> Seq Scan on board (C=0.00..27.50 R=1) (A=24.51..42824.35 45508/1)
Filter: ((thread < 45718) AND (reply < 1) AND (stat = 1))
Total runtime: 45043.93 msec
SELECT relpages,reltuples::INTEGER,relhasindex FROM pg_class WHERE relname = 'board';
relpages | reltuples | relhasindex
----------+-----------+-------------
10 | 1000 | f
VACUUM board;
SELECT relpages,reltuples::INTEGER,relhasindex FROM pg_class WHERE relname = 'board';
relpages | reltuples | relhasindex
----------+-----------+-------------
37198 | 2525878 | f
QUERY PLAN
-----------------------------------------------------------------------------------
Limit (C=81474.22..81474.22 R=1) (A=44760.23..44760.24 1/1)
-> Sort (C=81474.22..81477.73 R=1403) (A=44760.21..44760.22 2/1)
Sort Key: thread
-> Seq Scan on board (C=0.00..81400.87 R=1403) (A=23.63..42531.64 45508/1)
Filter: ((thread < 45718) AND (reply < 1) AND (stat = 1))
Total runtime: 44767.31 msec
CREATE INDEX bt ON board(thread);
SELECT count(*) FROM pg_stats WHERE tablename = 'board';
count
-------
0
ANALYZE board;
SELECT count(*) FROM pg_stats WHERE tablename = 'board';
count
-------
8
QUERY PLAN
------------------------------------------------------------------------------------------
Limit (C=0.00..0.50 R=1) (A=0.28..0.94 1/1)
-> Index Scan Backward using bt on board (C=0.00..83930.71 R=168360) (A=0.27..0.93 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 1.14 msec
How about next?
EXPLAIN ANALYZE SELECT post, thread, sub FROM board WHERE thread > 45718 AND reply<1 AND stat=1 ORDER BY thread ASC LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------
Limit (C=0.00..0.50 R=1) (A=0.50..2.69 1/1)
-> Index Scan using bt on board (C=0.00..7192.66 R=14425) (A=0.49..2.68 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 2.91 msec
UNION?
EXPLAIN ANALYZE SELECT * FROM (SELECT post, thread, sub FROM board WHERE thread < 45718 AND reply<1 AND stat=1 ORDER BY thread DESC LIMIT 1) AS uno UNION SELECT * FROM (SELECT post, thread, sub FROM board WHERE thread > 45718 AND reply<1 AND stat=1 ORDER BY thread ASC LIMIT 1) AS dos;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Unique (C=1.01..1.03 R=1) (A=3.85..3.87 2/1)
-> Sort (C=1.01..1.01 R=2) (A=3.84..3.85 2/1)
Sort Key: post, thread, sub
-> Append (C=0.00..1.00 R=2) (A=0.34..3.54 2/1)
-> Subquery Scan "*SELECT* 1" (C=0.00..0.50 R=1) (A=0.33..1.01 1/1)
-> Subquery Scan uno (C=0.00..0.50 R=1) (A=0.31..0.98 1/1)
-> Limit (C=0.00..0.50 R=1) (A=0.29..0.96 1/1)
-> Index Scan Backward using bt on board (C=0.00..83930.71 R=168360) (A=0.28..0.94 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Subquery Scan "*SELECT* 2" (C=0.00..0.50 R=1) (A=0.51..2.52 1/1)
-> Subquery Scan dos (C=0.00..0.50 R=1) (A=0.50..2.50 1/1)
-> Limit (C=0.00..0.50 R=1) (A=0.49..2.48 1/1)
-> Index Scan using bt on board (C=0.00..7192.66 R=14425) (A=0.48..2.47 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 5.36 msec
Subselects: thread
EXPLAIN ANALYZE SELECT post, thread, sub FROM board WHERE reply<1 AND stat=1 AND ( thread = (SELECT thread FROM board WHERE thread < 45718 AND reply<1 AND stat=1 ORDER BY thread DESC LIMIT 1) OR thread = (SELECT thread FROM board WHERE thread > 45718 AND reply<1 AND stat=1 ORDER BY thread ASC LIMIT 1) );
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using bt, bt on board (C=0.00..8.53 R=8) (A=4.00..5.45 2/1)
Index Cond: ((thread = $0) OR (thread = $1))
Filter: ((reply < 1) AND (stat = 1))
InitPlan
-> Limit (C=0.00..0.50 R=1) (A=0.35..1.01 1/1)
-> Index Scan Backward using bt on board (C=0.00..83930.71 R=168360) (A=0.34..1.00 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.50 R=1) (A=0.50..2.61 1/1)
-> Index Scan using bt on board (C=0.00..7192.66 R=14425) (A=0.49..2.59 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 6.07 msec
Subselects: unique (remove outer constraints)
EXPLAIN ANALYZE
SELECT post, thread, sub FROM board WHERE (
post = (SELECT post FROM board WHERE thread < 45718 AND reply<1 AND stat=1
ORDER BY thread DESC LIMIT 1)
OR
post = (SELECT post FROM board WHERE thread > 45718 AND reply<1 AND stat=1
ORDER BY thread ASC LIMIT 1)
);
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on board (C=0.00..76290.68 R=2) (A=40679.22..44606.79 2/1)
Filter: ((post = $0) OR (post = $1))
InitPlan
-> Limit (C=0.00..0.50 R=1) (A=13.89..14.52 1/1)
-> Index Scan Backward using bt on board (C=0.00..83930.71 R=168360) (A=13.88..14.50 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.50 R=1) (A=0.66..3.08 1/1)
-> Index Scan using bt on board (C=0.00..7192.66 R=14425) (A=0.65..3.07 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 44607.45 msec
CREATE INDEX board_post ON board(post);
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using board_post, board_post on board (C=0.00..6.03 R=2) (A=3.60..3.73 2/1)
Index Cond: ((post = $0) OR (post = $1))
InitPlan
-> Limit (C=0.00..0.50 R=1) (A=0.30..0.93 1/1)
-> Index Scan Backward using bt on board (C=0.00..82405.93 R=163189) (A=0.29..0.92 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.51 R=1) (A=0.47..2.47 1/1)
-> Index Scan using bt on board (C=0.00..7061.81 R=13982) (A=0.46..2.46 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 4.31 msec
Subselect: oid
DROP INDEX board_post; CREATE INDEX board_oid ON board(oid);
EXPLAIN ANALYZE
SELECT post, thread, sub FROM board WHERE (
oid = (SELECT oid FROM board WHERE thread < 45718 AND reply<1 AND stat=1
ORDER BY thread DESC LIMIT 1)
OR
oid = (SELECT oid FROM board WHERE thread > 45718 AND reply<1 AND stat=1
ORDER BY thread ASC LIMIT 1)
);
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using board_oid, board_oid on board (C=0.00..12.03 R=2) (A=3.62..3.76 2/1)
Index Cond: ((oid = $0) OR (oid = $1))
InitPlan
-> Limit (C=0.00..0.50 R=1) (A=0.33..1.09 1/1)
-> Index Scan Backward using bt on board (C=0.00..82405.93 R=163189) (A=0.32..1.07 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.51 R=1) (A=0.44..2.33 1/1)
-> Index Scan using bt on board (C=0.00..7061.81 R=13982) (A=0.44..2.32 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 4.44 msec
DROP INDEX board_oid;
Subselect: ctid
EXPLAIN ANALYZE
SELECT post, thread, sub FROM board WHERE (
ctid = (SELECT ctid FRM board WHERE thread < 45718 AND reply<1 AND stat=1
ORDER BY thread DESC LIMIT 1)
OR
ctid = (SELECT ctid FROM board WHERE thread > 45718 AND reply<1 AND stat=1
ORDER BY thread ASC LIMIT 1)
);
QUERY PLAN
----------------------------------------------------------------------------------------------
Tid Scan on board (C=0.00..8.03 R=2) (A=0.05..0.10 2/1)
Filter: ((ctid = $0) OR (ctid = $1))
InitPlan
-> Limit (C=0.00..0.50 R=1) (A=0.31..0.93 1/1)
-> Index Scan Backward using bt on board (C=0.00..82405.93 R=163189) (A=0.30..0.92 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.51 R=1) (A=0.47..2.40 1/1)
-> Index Scan using bt on board (C=0.00..7061.81 R=13982) (A=0.47..2.38 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 4.11 msec
WHERE clause: checking on the stats
SELECT * FROM pg_stats WHERE tablename='board' AND attname IN ('thread','reply','stat');
-[ RECORD 1 ]-----+-------------------------------------------------------------
schemaname | public
tablename | board
attname | thread
null_frac | 0
avg_width | 4
n_distinct | 43220
most_common_vals | {18411,26484,43330,45476,410,680,697,732,847,1038}
most_common_freqs | {0.001,0.001,0.001,0.001,0.000666667,0.000666667,0.000666667,
0.000666667,0.000666667,0.000666667}
histogram_bounds | {49,4816,9677,14834,19891,24643,29448,34548,39420,44622,49983}
correlation | 1
-[ RECORD 2 ]-----+-------------------------------------------------------------
schemaname | public
tablename | board
attname | reply
null_frac | 0
avg_width | 2
n_distinct | 144
most_common_vals | {1,9,4,3,6,7,2,10,17,12}
most_common_freqs | {0.023,0.023,0.022,0.0203333,0.02,0.02,0.0196667,
0.0196667,0.0196667,0.0193333}
histogram_bounds | {-95,13,19,24,30,36,43,50,59,70,98}
correlation | -0.00176567
-[ RECORD 3 ]-----+-------------------------------------------------------------
schemaname | public
tablename | board
attname | stat
null_frac | 0
avg_width | 2
n_distinct | 2
most_common_vals | {1,0}
most_common_freqs | {0.994667,0.00533333}
histogram_bounds |
correlation | 0.98684
SELECT thread, oid, ctid FROM board WHERE thread BETWEEN 45718-1 AND 45718+1 AND reply<1; thread | oid | ctid --------+---------+------------ 45717 | 7982867 | (33931,72) 45718 | 7982888 | (33932,7) 45719 | 7982953 | (33932,72)
Aside: ctid and physical ordering
Gathering more statistics
ALTER TABLE board ALTER COLUMN thread SET STATISTICS 20; ANALYZE board(thread); SELECT * FROM pg_stats WHERE tablename='board' AND attname = 'thread'; -[ RECORD 1 ]-----+---------------------------------- attname | thread n_distinct | 39367 most_common_vals | {7346,745,1329,1970,2107,3440,7277,8477,8589,10275,11549, 13127,14170,14656,15792,16472,17312,18232,22744,26834} most_common_freqs | {0.000666667,0.0005,0.0005,0.0005,0.0005,0.0005,0.0005, 0.0005,0.0005,0.0005,0.0005,0.0005,0.0005,0.0005,0.0005, 0.0005,0.0005,0.0005,0.0005,0.0005} histogram_bounds | {4,2585,5165,7593,10179,12511,15100,17577,19931,22390,25073, 27561,30556,33131,35581,37867,40279,42466,45261,47463,50000} correlation | 1
ALTER TABLE board ALTER COLUMN thread SET STATISTICS 50; ANALYZE board(thread);
SELECT * FROM pg_stats WHERE tablename='board' AND attname = 'thread';
-[ RECORD 1 ]-----+-----------------------
attname | thread
n_distinct | 37344
most_common_vals | {8654,26646,13670,2169,2426,2561,3018,3542,4371,7325,7738,8372,
10410,11991,13708,16241,16815,17240,17319,19078,20324,21784,22392,25006,26114,27359,
28234,29576,32571,34708,35910,36954,40188,42939,44528,47152,48220,49597,1139,1198,
1494,1570,1584,1800,1871,1901,1913,1924,1990,2033}
most_common_freqs | {0.0004,0.0004,0.000333333,0.000266667,0.000266667,0.000266667,
0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,
0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,
0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,
0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,0.000266667,
0.000266667,0.000266667,0.000266667,0.000266667,0.0002,0.0002,0.0002,0.0002,0.0002,
0.0002,0.0002,0.0002,0.0002,0.0002,0.0002,0.0002}
histogram_bounds | {9,1359,2283,3138,4057,5051,6030,7045,8011,8941,10023,11096,12007,
13014,14031,14970,15989,16901,17902,18978,19996,20919,21920,22868,23903,24942,25971,
26966,28002,28939,29978,31006,32009,33077,33951,35069,36043,37035,38046,38994,39952,
41011,41957,43040,44010,44991,46013,46960,47928,48923,49988}
correlation | 0.999614
VACUUM FULL board;
SELECT stat, COUNT(*) FROM board GROUP BY 1;
stat | count
------+---------
0 | 11915
1 | 2513656
2 | 307
SELECT COUNT(*) FROM board WHERE reply<1
UNION
SELECT COUNT(*) FROM board WHERE reply>1;
count
---------
50000
2426374
CREATE INDEX bt2 ON board(thread) WHERE reply<1;
SELECT relname,relpages,reltuples::INTEGER FROM pg_class
WHERE relname IN ('board','bt','bt2');
relname | relpages | reltuples
---------+----------+-----------
board | 37196 | 2525878
bt | 5534 | 2525878
bt2 | 112 | 50000
Subselect: ctid with conditional index
EXPLAIN
SELECT post, thread, sub FROM board WHERE (
ctid = (SELECT ctid FRM board WHERE thread < 45718 AND reply<1 AND stat=1
ORDER BY thread DESC LIMIT 1)
OR
ctid = (SELECT ctid FROM board WHERE thread > 45718 AND reply<1 AND stat=1
ORDER BY thread ASC LIMIT 1)
);
QUERY PLAN
------------------------------------------------------------------------------
Tid Scan on board (C=0.00..8.03 R=2)
Filter: ((ctid = $0) OR (ctid = $1))
InitPlan
-> Limit (C=0.00..0.11 R=1)
-> Index Scan Backward using bt2 on board (C=0.00..5616.68 R=50000)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.04 R=1)
-> Index Scan using bt2 on board (C=0.00..587.21 R=15143)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
DROP INDEX bt;
QUERY PLAN
--------------------------------------------------------------------------------------------
Tid Scan on board (C=0.00..8.03 R=2) (A=0.04..0.08 2/1)
Filter: ((ctid = $0) OR (ctid = $1))
InitPlan
-> Limit (C=0.00..0.11 R=1) (A=0.14..0.17 1/1)
-> Index Scan Backward using bt2 on board (C=0.00..5616.68 R=50000) (A=0.13..0.16 2/1)
Index Cond: (thread < 45718)
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.04 R=1) (A=0.09..0.12 1/1)
-> Index Scan using bt2 on board (C=0.00..587.21 R=15143) (A=0.08..0.11 2/1)
Index Cond: (thread > 45718)
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 0.76 msec
CREATE INDEX bt3 on board(thread) WHERE reply<1 AND thread BETWEEN 45000 AND 46000;
SELECT relpages,reltuples FROM pg_class WHERE relname='bt3';
relpages | reltuples
----------+-----------
5 | 1001
EXPLAIN ANALYZE
SELECT post, thread, sub FROM board WHERE (
ctid = (SELECT ctid FROM board WHERE thread BETWEEN 45001 AND 45718-1
AND reply<1 AND stat=1 ORDER BY thread DESC LIMIT 1)
OR
ctid = (SELECT ctid FROM board WHERE thread BETWEEN 45718+1 AND 46000
AND reply<1 AND stat=1 ORDER BY thread ASC LIMIT 1)
);
QUERY PLAN
-----------------------------------------------------------------------------------------
Tid Scan on board (C=0.00..8.03 R=2) (A=0.04..0.08 2/1)
Filter: ((ctid = $0) OR (ctid = $1))
InitPlan
-> Limit (C=0.00..0.10 R=1) (A=0.13..0.17 1/1)
-> Index Scan Backward using bt3 on board (C=0.00..102.93 R=1001) (A=0.12..0.15 2/1)
Index Cond: ((thread >= 45001) AND (thread <= 45717))
Filter: ((reply < 1) AND (stat = 1))
-> Limit (C=0.00..0.05 R=1) (A=0.08..0.12 1/1)
-> Index Scan using bt3 on board (C=0.00..46.75 R=1001) (A=0.08..0.11 2/1)
Index Cond: ((thread >= 45719) AND (thread <= 46000))
Filter: ((reply < 1) AND (stat = 1))
Total runtime: 0.75 msec
Previous: 'Child and Parent' Query [Table of Contents] Next: Physical ordering and ctid