Re: PostgreSQL does CAST implicitely between int and a domain derived from int - Mailing list pgsql-performance

Dear Kevin,

Thanks for help!

Could you run this?:
>
> set work_mem = '50MB';
> set effective_cache_size = '3GB';
> EXPLAIN ANALYZE <your query>
> begin transaction;
> drop index node_comment_statistics_node_comment_timestamp_idx;
> EXPLAIN ANALYZE <your query>
> rollback transaction;

set work_mem = '50MB';
set effective_cache_size = '1GB';

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=455.234..455.234 rows=0 loops=1)"
"  ->  Nested Loop  (cost=0.00..49565.19 rows=91 width=17) (actual
time=455.232..455.232 rows=0 loops=1)"
"        ->  Nested Loop  (cost=0.00..49538.56 rows=91 width=21) (actual
time=455.232..455.232 rows=0 loops=1)"
"              ->  Nested Loop  (cost=0.00..49512.17 rows=91 width=13)
(actual time=455.232..455.232 rows=0 loops=1)"
"                    ->  Nested Loop  (cost=0.00..27734.58 rows=67486
width=17) (actual time=0.027..264.540 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.618 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: 455.311 ms"

> begin transaction;
> drop index node_comment_statistics_node_comment_timestamp_idx;
> EXPLAIN ANALYZE <your query>
> rollback transaction;

begin transaction;
drop index node_comment_statistics_node_comment_timestamp_idx;

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 ;

rollback transaction;

Does not show any result because of ROLLBACK;
The query executes in 89 ms.

> Some configuration options can be dynamically overridden for a
> particular connection.  This is not a complete list of what you might
> want to use in your postgresql.conf file, but it might turn up an
> interesting plan for diagnostic purposes.

I am turning to configurator, stay tuned.
Again,thanks for your help.

Bye, Jean-Michel