Re: PostgreSQL does CAST implicitely between int and adomain derived from int

Le jeudi 27 août 2009 à 09:16 -0500, Kevin Grittner a écrit :
> Then you need to use a better tool to run it.

Understood, thanks.

cms=# set work_mem = '50MB';
SET
cms=# set effective_cache_size = '1GB';
SET
cms=# begin transaction;
BEGIN
cms=# drop index node_comment_statistics_node_comment_timestamp_idx;
DROP INDEX
cms=#
cms=# EXPLAIN ANALYSE
cms-# 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
cms-# FROM node n
cms-# INNER JOIN users u1 ON n.uid = u1.uid
cms-# INNER JOIN term_node tn ON n.vid = tn.vid
cms-# INNER JOIN node_comment_statistics ncs ON n.nid = ncs.nid
cms-# INNER JOIN users u2 ON ncs.last_comment_uid=u2.uid
cms-# WHERE n.status = 1 AND tn.tid = 3
cms-# ORDER BY ncs.last_comment_timestamp DESC LIMIT 1 OFFSET 0 ;
cms=# rollback transaction;

QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=972.82..972.82 rows=1 width=17) (actual time=0.018..0.018
rows=0 loops=1)
-> Sort (cost=972.82..973.04 rows=91 width=17) (actual
time=0.018..0.018 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.010..0.010 rows=0 loops=1)
-> Nested Loop (cost=4.96..945.74 rows=91 width=21)
(actual time=0.010..0.010 rows=0 loops=1)
-> Nested Loop (cost=4.96..919.34 rows=91
width=13) (actual time=0.010..0.010 rows=0 loops=1)
-> Nested Loop (cost=4.96..890.02 rows=91
width=8) (actual time=0.009..0.009 rows=0 loops=1)
-> Bitmap Heap Scan on term_node tn
(cost=4.96..215.63 rows=91 width=4) (actual time=0.009..0.009 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.008..0.008 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.092 ms
(22 lignes)

Does it mean my index is broken and should be rebuilt?

Kind regards,
Jean-Michel