Children posts
EXPLAIN ANALYZE
SELECT thread,reply,sub,post,
to_char(ctime,'FMHH:MI AM on FMDay, FMMonth DD, YYYY')
FROM b1
WHERE post IN (1234,1233,1231,1230,1229,1226,1200,1187,1185,343,342,341)
AND stat=1
QUERY PLAN
------------------------------------------------------------------------------------------
Index Scan using b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p on b1
(0.00..36.71 12/51) (0.13..0.70 12/1)
Index Cond: ((post = 1234) OR (post = 1233) OR (post = 1231) OR (post = 1230) OR (post = 1229) OR
(post = 1226) OR (post = 1200) OR (post = 1187) OR (post = 1185) OR (post = 343) OR
(post = 342) OR (post = 341))
Filter: (stat = 1)
Total runtime: 0.88 msec
Parent posts
EXPLAIN ANALYZE SELECT thread FROM b1 WHERE post IN (1234,1233,1231,1230,1229,1226,1200,1187,1185,343,342,341) AND stat=1
QUERY PLAN
-----------------------------------------------------------------------------------
Index Scan using b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p on b1
(0.00..36.71 12/4) (0.06..0.35 12/1)
Index Cond: ((post = 1234) OR (post = 1233) OR (post = 1231) OR (post = 1230) OR
(post = 1229) OR (post = 1226) OR (post = 1200) OR (post = 1187) OR
(post = 1185) OR (post = 343) OR (post = 342) OR (post = 341))
Filter: (stat = 1)
Total runtime: 0.52 msec
Combined?
EXPLAIN ANALYZE
SELECT thread
FROM b1
WHERE
( post IN (1234,1233,1231,1230,1229,1226,1200,1187,1185,343,342,341)
AND stat=1
)
OR
( thread IN
( SELECT thread FROM b1
WHERE post IN (1234,1233,1231,1230,1229,1226,1200,1187,1185,343,342,341)
AND stat=1
)
)
;
QUERY PLAN
-----------------------------------------------------------------------------------------
Seq Scan on b1 (0.00..1638254.77 22223/4) (12.25..1571.55 59/1)
Filter: (((post = 1234) AND (stat = 1)) OR ((post = 1233) AND (stat = 1)) OR ((post = 1231) AND
(stat = 1)) OR ((post = 1230) AND (stat = 1)) OR ((post = 1229) AND (stat = 1)) OR
((post = 1226) AND (stat = 1)) OR ((post = 1200) AND (stat = 1)) OR ((post = 1187) AND
(stat = 1)) OR ((post = 1185) AND (stat = 1)) OR ((post = 343) AND (stat = 1)) OR
((post = 342) AND (stat = 1)) OR ((post = 341) AND (stat = 1)) OR (subplan))
SubPlan
-> Materialize (36.71..36.71 12/4) (0.00..0.01 12/44422)
-> Index Scan using b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p on b1
(0.00..36.71 12/4) (0.05..0.34 12/1)
Index Cond: ((post = 1234) OR (post = 1233) OR (post = 1231) OR (post = 1230) OR
(post = 1229) OR (post = 1226) OR (post = 1200) OR (post = 1187) OR
(post = 1185) OR (post = 343) OR (post = 342) OR (post = 341))
Filter: (stat = 1)
Total runtime: 1571.82 msec
ctid?
EXPLAIN ANALYZE
SELECT thread
FROM b1
WHERE
( post IN (1234,1233,1231,1230,1229,1226,1200,1187,1185,343,342,341)
AND stat=1
)
OR
( ctid IN
( SELECT ctid FROM b1
WHERE post IN (1234,1233,1231,1230,1229,1226,1200,1187,1185,343,342,341)
AND stat=1
)
)
;
QUERY PLAN
----------------------------------------------------------------------------------
Seq Scan on b1 (0.00..1638254.77 22223/4) (12.91..1629.42 12/1)
Filter: (((post = 1234) AND (stat = 1)) OR ((post = 1233) AND (stat = 1)) OR ((post = 1231) AND
(stat = 1)) OR ((post = 1230) AND (stat = 1)) OR ((post = 1229) AND (stat = 1)) OR
((post = 1226) AND (stat = 1)) OR ((post = 1200) AND (stat = 1)) OR ((post = 1187) AND
(stat = 1)) OR ((post = 1185) AND (stat = 1)) OR ((post = 343) AND (stat = 1)) OR
((post = 342) AND (stat = 1)) OR ((post = 341) AND (stat = 1)) OR (subplan))
SubPlan
-> Materialize (36.71..36.71 12/6) (0.00..0.01 12/44422)
-> Index Scan using b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p, b1p on b1
(0.00..36.71 12/6) (0.05..0.34 12/1)
Index Cond: ((post = 1234) OR (post = 1233) OR (post = 1231) OR (post = 1230) OR
(post = 1229) OR (post = 1226) OR (post = 1200) OR (post = 1187) OR
(post = 1185) OR (post = 343) OR (post = 342) OR (post = 341))
Filter: (stat = 1)
Total runtime: 1629.65 msec
Sometime the app is best!
...
for (@$index) {
$parent .= "$_->{thread}" if $_->{reply}>=1;
}
$parent =~ s/,$//;
AND thread IN (...)
SELECT thread,reply,sub,
to_char(ctime,'FMHH:MI AM on FMDay, FMMonth DD, YYYY'),post
FROM b1
WHERE thread IN (110,222,223,234)
AND reply<1
Index Scan using b1t, b1t, b1t, b1t on b1 (0.00..17.38 5/51) (0.13..0.33 4/1) Index Cond: ((thread = 110) OR (thread = 222) OR (thread = 223) OR (thread = 234)) Filter: (reply < 1) Total runtime: 0.44 msec
Previous: Taxonomy Query [Table of Contents] Next: 'Previous and Next' Query