2 SET pg_hint_plan.enable_hint TO on;
3 SET pg_hint_plan.debug_print TO on;
4 SET client_min_messages TO LOG;
5 SET search_path TO public;
7 ---- No. G-1-1 RULE definition table
10 EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
12 -----------------------------------------------------------------
15 Join Filter: (t1.c1 = t4.c1)
17 Join Filter: (t1.c1 = t3.c1)
19 Join Filter: (t1.c1 = t2.c1)
22 TID Cond: (ctid = '(1,1)'::tid)
25 TID Cond: (ctid = '(1,1)'::tid)
27 Filter: (ctid = '(1,1)'::tid)
29 TID Cond: (ctid = '(1,1)'::tid)
31 TID Cond: (ctid = '(1,1)'::tid)
35 Set(enable_tidscan off)Set(enable_nestloop off)
37 EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
40 Set(enable_nestloop off)
41 Set(enable_tidscan off)
47 ---------------------------------------------------------------------
51 Hash Cond: (t1.c1 = t4.c1)
53 Hash Cond: (t1.c1 = t2.c1)
55 Hash Cond: (t3.c1 = t1.c1)
57 Filter: (ctid = '(1,1)'::tid)
60 Filter: (ctid = '(1,1)'::tid)
63 Filter: (ctid = '(1,1)'::tid)
66 Filter: (ctid = '(1,1)'::tid)
68 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
71 EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
73 -----------------------------------------------------------------
76 Join Filter: (b1t1.c1 = b1t4.c1)
78 Join Filter: (b1t1.c1 = b1t3.c1)
80 Join Filter: (b1t1.c1 = b1t2.c1)
83 TID Cond: (ctid = '(1,1)'::tid)
85 -> Tid Scan on t1 b1t1
86 TID Cond: (ctid = '(1,1)'::tid)
87 -> Seq Scan on t2 b1t2
88 Filter: (ctid = '(1,1)'::tid)
89 -> Tid Scan on t3 b1t3
90 TID Cond: (ctid = '(1,1)'::tid)
91 -> Tid Scan on t4 b1t4
92 TID Cond: (ctid = '(1,1)'::tid)
96 Set(enable_tidscan off)Set(enable_nestloop off)
98 EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
101 Set(enable_nestloop off)
102 Set(enable_tidscan off)
108 ---------------------------------------------------------------------
112 Hash Cond: (b1t1.c1 = b1t4.c1)
114 Hash Cond: (b1t1.c1 = b1t2.c1)
116 Hash Cond: (b1t3.c1 = b1t1.c1)
117 -> Seq Scan on t3 b1t3
118 Filter: (ctid = '(1,1)'::tid)
120 -> Seq Scan on t1 b1t1
121 Filter: (ctid = '(1,1)'::tid)
123 -> Seq Scan on t2 b1t2
124 Filter: (ctid = '(1,1)'::tid)
126 -> Seq Scan on t4 b1t4
127 Filter: (ctid = '(1,1)'::tid)
129 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
133 EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
135 -----------------------------------------------------------------
138 Join Filter: (t1.c1 = t4.c1)
140 Join Filter: (t1.c1 = t3.c1)
142 Join Filter: (t1.c1 = t2.c1)
145 TID Cond: (ctid = '(1,1)'::tid)
148 TID Cond: (ctid = '(1,1)'::tid)
150 Filter: (ctid = '(1,1)'::tid)
152 TID Cond: (ctid = '(1,1)'::tid)
154 TID Cond: (ctid = '(1,1)'::tid)
158 Join Filter: (t1.c1 = t4.c1)
160 Join Filter: (t1.c1 = t3.c1)
162 Join Filter: (t1.c1 = t2.c1)
165 TID Cond: (ctid = '(1,1)'::tid)
168 TID Cond: (ctid = '(1,1)'::tid)
170 Filter: (ctid = '(1,1)'::tid)
172 TID Cond: (ctid = '(1,1)'::tid)
174 TID Cond: (ctid = '(1,1)'::tid)
178 Set(enable_tidscan off)Set(enable_nestloop off)
180 EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
183 Set(enable_nestloop off)
184 Set(enable_tidscan off)
191 Set(enable_nestloop off)
192 Set(enable_tidscan off)
198 ---------------------------------------------------------------------
202 Hash Cond: (t1.c1 = t4.c1)
204 Hash Cond: (t1.c1 = t2.c1)
206 Hash Cond: (t3.c1 = t1.c1)
208 Filter: (ctid = '(1,1)'::tid)
211 Filter: (ctid = '(1,1)'::tid)
214 Filter: (ctid = '(1,1)'::tid)
217 Filter: (ctid = '(1,1)'::tid)
219 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
224 Hash Cond: (t1.c1 = t4.c1)
226 Hash Cond: (t1.c1 = t2.c1)
228 Hash Cond: (t3.c1 = t1.c1)
230 Filter: (ctid = '(1,1)'::tid)
233 Filter: (ctid = '(1,1)'::tid)
236 Filter: (ctid = '(1,1)'::tid)
239 Filter: (ctid = '(1,1)'::tid)
241 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
244 EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
246 -----------------------------------------------------------------
249 Join Filter: (b1t1.c1 = b1t4.c1)
251 Join Filter: (b1t1.c1 = b1t3.c1)
253 Join Filter: (b1t1.c1 = b1t2.c1)
256 TID Cond: (ctid = '(1,1)'::tid)
258 -> Tid Scan on t1 b1t1
259 TID Cond: (ctid = '(1,1)'::tid)
260 -> Seq Scan on t2 b1t2
261 Filter: (ctid = '(1,1)'::tid)
262 -> Tid Scan on t3 b1t3
263 TID Cond: (ctid = '(1,1)'::tid)
264 -> Tid Scan on t4 b1t4
265 TID Cond: (ctid = '(1,1)'::tid)
269 Join Filter: (b2t1.c1 = b2t4.c1)
271 Join Filter: (b2t1.c1 = b2t3.c1)
273 Join Filter: (b2t1.c1 = b2t2.c1)
276 TID Cond: (ctid = '(1,1)'::tid)
278 -> Tid Scan on t1 b2t1
279 TID Cond: (ctid = '(1,1)'::tid)
280 -> Seq Scan on t2 b2t2
281 Filter: (ctid = '(1,1)'::tid)
282 -> Tid Scan on t3 b2t3
283 TID Cond: (ctid = '(1,1)'::tid)
284 -> Tid Scan on t4 b2t4
285 TID Cond: (ctid = '(1,1)'::tid)
289 Set(enable_tidscan off)Set(enable_nestloop off)
291 EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
294 Set(enable_nestloop off)
295 Set(enable_tidscan off)
302 Set(enable_nestloop off)
303 Set(enable_tidscan off)
309 ---------------------------------------------------------------------
313 Hash Cond: (b1t1.c1 = b1t4.c1)
315 Hash Cond: (b1t1.c1 = b1t2.c1)
317 Hash Cond: (b1t3.c1 = b1t1.c1)
318 -> Seq Scan on t3 b1t3
319 Filter: (ctid = '(1,1)'::tid)
321 -> Seq Scan on t1 b1t1
322 Filter: (ctid = '(1,1)'::tid)
324 -> Seq Scan on t2 b1t2
325 Filter: (ctid = '(1,1)'::tid)
327 -> Seq Scan on t4 b1t4
328 Filter: (ctid = '(1,1)'::tid)
330 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
335 Hash Cond: (b2t1.c1 = b2t4.c1)
337 Hash Cond: (b2t1.c1 = b2t2.c1)
339 Hash Cond: (b2t3.c1 = b2t1.c1)
340 -> Seq Scan on t3 b2t3
341 Filter: (ctid = '(1,1)'::tid)
343 -> Seq Scan on t1 b2t1
344 Filter: (ctid = '(1,1)'::tid)
346 -> Seq Scan on t2 b2t2
347 Filter: (ctid = '(1,1)'::tid)
349 -> Seq Scan on t4 b2t4
350 Filter: (ctid = '(1,1)'::tid)
352 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
356 EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
358 -----------------------------------------------------------------
361 Join Filter: (t1.c1 = t4.c1)
363 Join Filter: (t1.c1 = t3.c1)
365 Join Filter: (t1.c1 = t2.c1)
368 TID Cond: (ctid = '(1,1)'::tid)
371 TID Cond: (ctid = '(1,1)'::tid)
373 Filter: (ctid = '(1,1)'::tid)
375 TID Cond: (ctid = '(1,1)'::tid)
377 TID Cond: (ctid = '(1,1)'::tid)
381 Join Filter: (t1.c1 = t4.c1)
383 Join Filter: (t1.c1 = t3.c1)
385 Join Filter: (t1.c1 = t2.c1)
388 TID Cond: (ctid = '(1,1)'::tid)
391 TID Cond: (ctid = '(1,1)'::tid)
393 Filter: (ctid = '(1,1)'::tid)
395 TID Cond: (ctid = '(1,1)'::tid)
397 TID Cond: (ctid = '(1,1)'::tid)
401 Join Filter: (t1.c1 = t4.c1)
403 Join Filter: (t1.c1 = t3.c1)
405 Join Filter: (t1.c1 = t2.c1)
408 TID Cond: (ctid = '(1,1)'::tid)
411 TID Cond: (ctid = '(1,1)'::tid)
413 Filter: (ctid = '(1,1)'::tid)
415 TID Cond: (ctid = '(1,1)'::tid)
417 TID Cond: (ctid = '(1,1)'::tid)
421 Set(enable_tidscan off)Set(enable_nestloop off)
423 EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
426 Set(enable_nestloop off)
427 Set(enable_tidscan off)
434 Set(enable_nestloop off)
435 Set(enable_tidscan off)
442 Set(enable_nestloop off)
443 Set(enable_tidscan off)
449 ---------------------------------------------------------------------
453 Hash Cond: (t1.c1 = t4.c1)
455 Hash Cond: (t1.c1 = t2.c1)
457 Hash Cond: (t3.c1 = t1.c1)
459 Filter: (ctid = '(1,1)'::tid)
462 Filter: (ctid = '(1,1)'::tid)
465 Filter: (ctid = '(1,1)'::tid)
468 Filter: (ctid = '(1,1)'::tid)
470 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
475 Hash Cond: (t1.c1 = t4.c1)
477 Hash Cond: (t1.c1 = t2.c1)
479 Hash Cond: (t3.c1 = t1.c1)
481 Filter: (ctid = '(1,1)'::tid)
484 Filter: (ctid = '(1,1)'::tid)
487 Filter: (ctid = '(1,1)'::tid)
490 Filter: (ctid = '(1,1)'::tid)
492 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
497 Hash Cond: (t1.c1 = t4.c1)
499 Hash Cond: (t1.c1 = t2.c1)
501 Hash Cond: (t3.c1 = t1.c1)
503 Filter: (ctid = '(1,1)'::tid)
506 Filter: (ctid = '(1,1)'::tid)
509 Filter: (ctid = '(1,1)'::tid)
512 Filter: (ctid = '(1,1)'::tid)
514 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
517 EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
519 -----------------------------------------------------------------
522 Join Filter: (b1t1.c1 = b1t4.c1)
524 Join Filter: (b1t1.c1 = b1t3.c1)
526 Join Filter: (b1t1.c1 = b1t2.c1)
529 TID Cond: (ctid = '(1,1)'::tid)
531 -> Tid Scan on t1 b1t1
532 TID Cond: (ctid = '(1,1)'::tid)
533 -> Seq Scan on t2 b1t2
534 Filter: (ctid = '(1,1)'::tid)
535 -> Tid Scan on t3 b1t3
536 TID Cond: (ctid = '(1,1)'::tid)
537 -> Tid Scan on t4 b1t4
538 TID Cond: (ctid = '(1,1)'::tid)
542 Join Filter: (b2t1.c1 = b2t4.c1)
544 Join Filter: (b2t1.c1 = b2t3.c1)
546 Join Filter: (b2t1.c1 = b2t2.c1)
549 TID Cond: (ctid = '(1,1)'::tid)
551 -> Tid Scan on t1 b2t1
552 TID Cond: (ctid = '(1,1)'::tid)
553 -> Seq Scan on t2 b2t2
554 Filter: (ctid = '(1,1)'::tid)
555 -> Tid Scan on t3 b2t3
556 TID Cond: (ctid = '(1,1)'::tid)
557 -> Tid Scan on t4 b2t4
558 TID Cond: (ctid = '(1,1)'::tid)
562 Join Filter: (b3t1.c1 = b3t4.c1)
564 Join Filter: (b3t1.c1 = b3t3.c1)
566 Join Filter: (b3t1.c1 = b3t2.c1)
569 TID Cond: (ctid = '(1,1)'::tid)
571 -> Tid Scan on t1 b3t1
572 TID Cond: (ctid = '(1,1)'::tid)
573 -> Seq Scan on t2 b3t2
574 Filter: (ctid = '(1,1)'::tid)
575 -> Tid Scan on t3 b3t3
576 TID Cond: (ctid = '(1,1)'::tid)
577 -> Tid Scan on t4 b3t4
578 TID Cond: (ctid = '(1,1)'::tid)
582 Set(enable_tidscan off)Set(enable_nestloop off)
584 EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
587 Set(enable_nestloop off)
588 Set(enable_tidscan off)
595 Set(enable_nestloop off)
596 Set(enable_tidscan off)
603 Set(enable_nestloop off)
604 Set(enable_tidscan off)
610 ---------------------------------------------------------------------
614 Hash Cond: (b1t1.c1 = b1t4.c1)
616 Hash Cond: (b1t1.c1 = b1t2.c1)
618 Hash Cond: (b1t3.c1 = b1t1.c1)
619 -> Seq Scan on t3 b1t3
620 Filter: (ctid = '(1,1)'::tid)
622 -> Seq Scan on t1 b1t1
623 Filter: (ctid = '(1,1)'::tid)
625 -> Seq Scan on t2 b1t2
626 Filter: (ctid = '(1,1)'::tid)
628 -> Seq Scan on t4 b1t4
629 Filter: (ctid = '(1,1)'::tid)
631 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
636 Hash Cond: (b2t1.c1 = b2t4.c1)
638 Hash Cond: (b2t1.c1 = b2t2.c1)
640 Hash Cond: (b2t3.c1 = b2t1.c1)
641 -> Seq Scan on t3 b2t3
642 Filter: (ctid = '(1,1)'::tid)
644 -> Seq Scan on t1 b2t1
645 Filter: (ctid = '(1,1)'::tid)
647 -> Seq Scan on t2 b2t2
648 Filter: (ctid = '(1,1)'::tid)
650 -> Seq Scan on t4 b2t4
651 Filter: (ctid = '(1,1)'::tid)
653 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
658 Hash Cond: (b3t1.c1 = b3t4.c1)
660 Hash Cond: (b3t1.c1 = b3t2.c1)
662 Hash Cond: (b3t3.c1 = b3t1.c1)
663 -> Seq Scan on t3 b3t3
664 Filter: (ctid = '(1,1)'::tid)
666 -> Seq Scan on t1 b3t1
667 Filter: (ctid = '(1,1)'::tid)
669 -> Seq Scan on t2 b3t2
670 Filter: (ctid = '(1,1)'::tid)
672 -> Seq Scan on t4 b3t4
673 Filter: (ctid = '(1,1)'::tid)
675 Filter: ((ctid = '(1,1)'::tid) AND (c1 = 1))
678 RESET client_min_messages;
680 ---- No. G-2-1 GUC parameter
683 /*+Set(1234567890123456789012345678901234567890123456789012345678901234 1)*/
684 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
685 NOTICE: identifier "1234567890123456789012345678901234567890123456789012345678901234" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
686 INFO: unrecognized configuration parameter "123456789012345678901234567890123456789012345678901234567890123"
693 /*+Set(constraint_exclusion 1234567890123456789012345678901234567890123456789012345678901234)*/
694 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
695 INFO: invalid value for parameter "constraint_exclusion": "1234567890123456789012345678901234567890123456789012345678901234"
696 HINT: Available values: partition, on, off.
703 ---- No. G-2-2 category of GUC parameter and role
707 /*+Set(block_size 16384)*/
708 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
709 INFO: parameter "block_size" cannot be changed
716 /*+Set(archive_mode off)*/
717 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
718 INFO: parameter "archive_mode" cannot be changed without restarting the server
725 /*+Set(archive_timeout 0)*/
726 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
727 INFO: parameter "archive_timeout" cannot be changed now
734 /*+Set(log_connections off)*/
735 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
736 INFO: parameter "log_connections" cannot be set after connection start
743 /*+Set(log_min_messages WARNING)*/
744 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
752 GRANT ALL ON SCHEMA s1 TO PUBLIC;
753 GRANT SELECT ON ALL TABLES IN SCHEMA s1 TO normal_user;
754 SET ROLE normal_user;
755 /*+Set(log_min_messages WARNING)*/
756 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
757 INFO: permission denied to set parameter "log_min_messages"
764 /*+Set(enable_seqscan on)*/
765 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
772 REVOKE SELECT ON ALL TABLES IN SCHEMA s1 FROM normal_user;
773 REVOKE ALL ON SCHEMA s1 FROM PUBLIC;
775 ---- No. G-2-3 conflict set hint
777 SET client_min_messages TO LOG;
779 /*+Set(enable_indexscan on)Set(enable_indexscan off)*/
780 SELECT * FROM s1.t1 WHERE false;
781 INFO: pg_hint_plan: hint syntax error at or near "Set(enable_indexscan on)Set(enable_indexscan off)"
782 DETAIL: Conflict set hint.
785 Set(enable_indexscan off)
788 Set(enable_indexscan on)
796 /*+Set(client_min_messages DEBUG5)Set(client_min_messages WARNING)Set(client_min_messages DEBUG2)*/
797 SELECT * FROM s1.t1 WHERE false;
798 INFO: pg_hint_plan: hint syntax error at or near "Set(client_min_messages DEBUG5)Set(client_min_messages WARNING)Set(client_min_messages DEBUG2)"
799 DETAIL: Conflict set hint.
800 INFO: pg_hint_plan: hint syntax error at or near "Set(client_min_messages WARNING)Set(client_min_messages DEBUG2)"
801 DETAIL: Conflict set hint.
804 Set(client_min_messages DEBUG2)
807 Set(client_min_messages DEBUG5)
808 Set(client_min_messages WARNING)
816 /*+Set(enable_indexscan on)Set(enable_indexscan o)*/
817 SELECT * FROM s1.t1 WHERE false;
818 INFO: pg_hint_plan: hint syntax error at or near "Set(enable_indexscan on)Set(enable_indexscan o)"
819 DETAIL: Conflict set hint.
820 INFO: parameter "enable_indexscan" requires a Boolean value
825 Set(enable_indexscan on)
827 Set(enable_indexscan o)
834 /*+Set(client_min_messages DEBUG5)Set(client_min_messages WARNING)Set(client_min_messages DEBU)*/
835 SELECT * FROM s1.t1 WHERE false;
836 INFO: pg_hint_plan: hint syntax error at or near "Set(client_min_messages DEBUG5)Set(client_min_messages WARNING)Set(client_min_messages DEBU)"
837 DETAIL: Conflict set hint.
838 INFO: pg_hint_plan: hint syntax error at or near "Set(client_min_messages WARNING)Set(client_min_messages DEBU)"
839 DETAIL: Conflict set hint.
840 INFO: invalid value for parameter "client_min_messages": "DEBU"
841 HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, notice, warning, error.
846 Set(client_min_messages DEBUG5)
847 Set(client_min_messages WARNING)
849 Set(client_min_messages DEBU)
856 ---- No. G-2-4 debug message
859 /*+SeqScan(a)IndexScan(a)SeqScan(c)NestLoop(a) */
860 SELECT * FROM s1.t1 a, s1.t2 b WHERE false;
861 INFO: pg_hint_plan: hint syntax error at or near " "
862 DETAIL: NestLoop hint requires at least two relations.
863 INFO: pg_hint_plan: hint syntax error at or near "SeqScan(a)IndexScan(a)SeqScan(c)NestLoop(a) "
864 DETAIL: Conflict scan method hint.
875 c1 | c2 | c3 | c4 | c1 | c2 | c3 | c4
876 ----+----+----+----+----+----+----+----