Re: PostgreSQL does CAST implicitely between int andadomain derived from int

Le jeudi 27 août 2009 à 09:52 -0500, Kevin Grittner a écrit :
> Just to get another data point, what happens if you run the same query
> without taking the index out of the picture, but without the LIMIT or
> OFFSET clauses? An EXPLAIN ANALYZE of that would help understand it
> more fully.

Also, just a short notice that this SELECT returns no result.

You were right: adding LIMIT 1 changes speed from O.090 ms to 420 ms.
This has nothing to do with casting.

EXPLAIN ANALYSE
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0,
u2.name, ncs.last_comment_name) AS last_comment_name,
ncs.last_comment_uid
FROM node n
INNER JOIN users u1 ON n.uid = u1.uid
INNER JOIN term_node tn ON n.vid = tn.vid
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid
WHERE n.status = 1 AND tn.tid = 3
ORDER BY ncs.last_comment_timestamp DESC

"Sort (cost=975.32..975.55 rows=91 width=17) (actual time=0.021..0.021
rows=0 loops=1)"
" Sort Key: ncs.last_comment_timestamp"
" Sort Method: quicksort Memory: 25kB"
" -> Nested Loop (cost=4.96..972.36 rows=91 width=17) (actual
time=0.016..0.016 rows=0 loops=1)"
" -> Nested Loop (cost=4.96..945.74 rows=91 width=21) (actual
time=0.016..0.016 rows=0 loops=1)"
" -> Nested Loop (cost=4.96..919.34 rows=91 width=13)
(actual time=0.016..0.016 rows=0 loops=1)"
" -> Nested Loop (cost=4.96..890.02 rows=91
width=8) (actual time=0.016..0.016 rows=0 loops=1)"
" -> Bitmap Heap Scan on term_node tn
(cost=4.96..215.63 rows=91 width=4) (actual time=0.016..0.016 rows=0
loops=1)"
" Recheck Cond: ((tid)::integer = 3)"
" -> Bitmap Index Scan on
term_node_tid_idx (cost=0.00..4.94 rows=91 width=0) (actual
time=0.014..0.014 rows=0 loops=1)"
" Index Cond: ((tid)::integer = 3)"
" -> Index Scan using node_vid_idx on node n
(cost=0.00..7.40 rows=1 width=12) (never executed)"
" Index Cond: ((n.vid)::integer =
(tn.vid)::integer)"
" Filter: (n.status = 1)"
" -> Index Scan using node_comment_statistics_pkey
on node_comment_statistics ncs (cost=0.00..0.31 rows=1 width=13) (never
executed)"
" Index Cond: ((ncs.nid)::integer = n.nid)"
" -> Index Scan using users_pkey on users u2
(cost=0.00..0.28 rows=1 width=12) (never executed)"
" Index Cond: (u2.uid = ncs.last_comment_uid)"
" -> Index Scan using users_pkey on users u1 (cost=0.00..0.28
rows=1 width=4) (never executed)"
" Index Cond: (u1.uid = n.uid)"
"Total runtime: 0.090 ms"

EXPLAIN ANALYSE
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0,
u2.name, ncs.last_comment_name) AS last_comment_name,
ncs.last_comment_uid
FROM node n
INNER JOIN users u1 ON n.uid = u1.uid
INNER JOIN term_node tn ON n.vid = tn.vid
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid
WHERE n.status = 1 AND tn.tid = 3
ORDER BY ncs.last_comment_timestamp DESC
LIMIT 1

"Limit (cost=0.00..544.67 rows=1 width=17) (actual
time=435.715..435.715 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..49565.19 rows=91 width=17) (actual
time=435.713..435.713 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..49538.56 rows=91 width=21) (actual
time=435.713..435.713 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..49512.17 rows=91 width=13)
(actual time=435.713..435.713 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..27734.58 rows=67486
width=17) (actual time=0.029..252.443 rows=67486 loops=1)"
" -> Index Scan Backward using
node_comment_statistics_node_comment_timestamp_idx on
node_comment_statistics ncs (cost=0.00..3160.99 rows=67486 width=13)
(actual time=0.014..40.583 rows=67486 loops=1)"
" -> Index Scan using node_pkey on node n
(cost=0.00..0.35 rows=1 width=12) (actual time=0.002..0.003 rows=1
loops=67486)"
" Index Cond: (n.nid =
(ncs.nid)::integer)"
" Filter: (n.status = 1)"
" -> Index Scan using term_node_vid_idx on term_node
tn (cost=0.00..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=0
loops=67486)"
" Index Cond: ((tn.vid)::integer =
(n.vid)::integer)"
" Filter: ((tn.tid)::integer = 3)"
" -> Index Scan using users_pkey on users u2
(cost=0.00..0.28 rows=1 width=12) (never executed)"
" Index Cond: (u2.uid = ncs.last_comment_uid)"
" -> Index Scan using users_pkey on users u1 (cost=0.00..0.28
rows=1 width=4) (never executed)"
" Index Cond: (u1.uid = n.uid)"
"Total runtime: 435.788 ms"

EXPLAIN ANALYSE
SELECT ncs.last_comment_timestamp, IF (ncs.last_comment_uid != 0,
u2.name, ncs.last_comment_name) AS last_comment_name,
ncs.last_comment_uid
FROM node n
INNER JOIN users u1 ON n.uid = u1.uid
INNER JOIN term_node tn ON n.vid = tn.vid
INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid
WHERE n.status = 1 AND tn.tid = 3
ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0

"Limit (cost=0.00..544.67 rows=1 width=17) (actual
time=541.488..541.488 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..49565.19 rows=91 width=17) (actual
time=541.486..541.486 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..49538.56 rows=91 width=21) (actual
time=541.485..541.485 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..49512.17 rows=91 width=13)
(actual time=541.485..541.485 rows=0 loops=1)"
" -> Nested Loop (cost=0.00..27734.58 rows=67486
width=17) (actual time=0.024..307.341 rows=67486 loops=1)"
" -> Index Scan Backward using
node_comment_statistics_node_comment_timestamp_idx on
node_comment_statistics ncs (cost=0.00..3160.99 rows=67486 width=13)
(actual time=0.012..62.504 rows=67486 loops=1)"
" -> Index Scan using node_pkey on node n
(cost=0.00..0.35 rows=1 width=12) (actual time=0.003..0.003 rows=1
loops=67486)"
" Index Cond: (n.nid =
(ncs.nid)::integer)"
" Filter: (n.status = 1)"
" -> Index Scan using term_node_vid_idx on term_node
tn (cost=0.00..0.31 rows=1 width=4) (actual time=0.003..0.003 rows=0
loops=67486)"
" Index Cond: ((tn.vid)::integer =
(n.vid)::integer)"
" Filter: ((tn.tid)::integer = 3)"
" -> Index Scan using users_pkey on users u2
(cost=0.00..0.28 rows=1 width=12) (never executed)"
" Index Cond: (u2.uid = ncs.last_comment_uid)"
" -> Index Scan using users_pkey on users u1 (cost=0.00..0.28
rows=1 width=4) (never executed)"
" Index Cond: (u1.uid = n.uid)"
"Total runtime: 541.568 ms"