_skipscan2.sql

application/sql

Download

CREATE TABLE skip_scan_test (a INTEGER, b INTEGER, c INTEGER);
CREATE INDEX abc_idx ON skip_scan_test USING btree (a, b, c);
CREATE INDEX bc_idx ON skip_scan_test USING btree (b, c);
CREATE INDEX c_idx ON skip_scan_test USING btree (c);
INSERT INTO skip_scan_test (a, b, c) VALUES
	(167, 139, 558),
	(235, NULL, 77),
	(724, 544, 394),
	(279, NULL, 609),
	(961, 547, 112),
	(625, 765, 287),
	(386, 685, 118),
	(237, NULL, 998),
	(553, 152, 775),
	(373, 672, 77),
	(587, 46, 528),
	(992, 533, 728),
	(813, NULL, 829),
	(NULL, 12, 682),
	(572, 716, 665),
	(NULL, 7, NULL),
	(735, 147, NULL),
	(206, 534, 907),
	(441, NULL, 394),
	(749, NULL, 812),
	(411, 454, 371),
	(22, 79, 725),
	(NULL, NULL, 933),
	(382, 865, 20),
	(400, 776, 791),
	(265, 90, 335),
	(943, 328, 271),
	(NULL, 627, 734),
	(622, 384, 938),
	(951, 999, 785),
	(963, 249, 72),
	(NULL, NULL, 280),
	(903, 340, 796),
	(963, 926, 797),
	(743, 48, 134),
	(790, 161, 862),
	(735, 986, 870),
	(199, 751, 977),
	(NULL, 957, 644),
	(200, 885, 618),
	(68, 533, 596),
	(670, 550, 819),
	(388, 274, 858),
	(369, 753, 360),
	(129, 970, 522),
	(NULL, 438, NULL),
	(291, NULL, 245),
	(135, 587, NULL),
	(76, 724, 936),
	(448, 290, 303),
	(415, 73, 339),
	(835, 374, 959),
	(285, 350, 716),
	(561, 776, 446),
	(593, 952, 780),
	(496, NULL, 141),
	(259, 943, 346),
	(868, 733, 171),
	(169, 861, 953),
	(237, 248, 521),
	(875, 641, 658),
	(723, 875, 239),
	(732, 586, 687),
	(210, 882, 862),
	(747, 635, 55),
	(935, 529, 507),
	(534, 129, 32),
	(303, 694, NULL),
	(72, 521, NULL),
	(240, 357, NULL),
	(862, NULL, 320),
	(223, 808, 946),
	(654, 961, 380),
	(807, 474, 913),
	(888, 878, 653),
	(193, NULL, 372),
	(988, 146, 511),
	(654, 867, 578),
	(628, 960, 676),
	(985, NULL, 308),
	(157, 926, 151),
	(699, 391, 180),
	(426, NULL, 516),
	(123, NULL, 480),
	(358, 924, 89),
	(508, 108, 813),
	(671, 467, 692),
	(124, 382, 951),
	(466, 49, 238),
	(977, 945, 717),
	(654, 610, 497),
	(894, 172, 341),
	(833, 605, 166),
	(245, 863, 244),
	(978, 166, 523),
	(930, 114, 661),
	(692, 789, 206),
	(NULL, 953, 505),
	(756, 300, 933),
	(73, 263, 932),
	(367, 877, 993),
	(36, 329, 513),
	(931, 368, 471),
	(464, 62, 226),
	(455, 866, 482),
	(769, 785, 966),
	(220, 5, NULL),
	(255, 445, 115);
ANALYZE skip_scan_test;
SELECT a, b, c
	FROM skip_scan_test
	WHERE c <= ANY(ARRAY[226, 203, 567])
	  AND b >= ANY(ARRAY[487, 812, 408, NULL, 414, 257, 253, 179, 437, 224, 940, 404, 135, 820, 633, 89, 307, 202, 171, 582, 811, 745, 586, 4])
	  AND a IS DISTINCT FROM 187
	  AND b IS DISTINCT FROM 712
	  AND a = ANY(ARRAY[419, 447, 514, 515, 870, 291, 218, 789, 117, 31, 236, 53, 233, 251, 832, 893, 189, 510, 629, 668, 987, 224, 639, 357, 510, 494, 984, 337, 336, 951, 744, 528, 791, 842, 730, 500, 473, 998, 916, 47, 553, 45, 643, 495, 220, 178, 712])
	  AND b != 238
		  AND c IS NOT DISTINCT FROM 131
	  AND b > ANY(ARRAY[429, NULL, 926, 9, 300, 406, 624, 334, 892, 180, 667, 827, 651, NULL, 660, 706])
	  AND b IS NOT DISTINCT FROM 760
	  AND a = ANY(ARRAY[558, 679, NULL, 107])
	ORDER BY b ASC, a DESC, c ASC, b ASC, c ASC, a DESC, a DESC, a DESC;