Re: join on three tables is slow - Mailing list pgsql-sql

Pavel Stehule wrote:
> Hello
>
> there is diference in agg position. Send, please, query and explain
> analyze output. And test id = any (... ) looks like hard
> denormalisation and can do problems. This condition can be slow and
> for large arrays is better use multivalues.
>
> SELECT *
>    FROM tab
>   WHERE x IN (VALUES(10),(20));
>
> it's faster when sizeof(array) > 80
>
> Regards
> Pavel Stehule
>
>
>
>

WITH e.active = true

=# EXPLAIN ANALYZE select p.addr, p.name, p.name2 from myfile as p join
res_partner as e on e.id in ( 3214, 3213, 3212, 3211, 3210, 3209, 3208,
3207, 3206, 3205, 3204, 3203, 3202, 3201, 3200, 3199, 3198, 3197, 3196,
3195, 3194, 3193, 3192, 3191, 3190, 3189, 3188, 3187, 3186, 3185, 3184,
3183, 3176, 3175, 3174, 3173, 3172, 3171, 3170, 3169, 3168, 3167, 3166,
3165, 3164, 3163, 3162, 3161, 3160, 3159, 3158, 3157, 3156, 3155, 3154,
3153, 3152, 3151, 3150, 3149, 3148, 3147, 3146, 3145, 3144, 3143, 3142,
3141, 3140, 3139, 3138, 3137, 3136, 3135, 3134, 3133, 3132, 3131, 3130,
3129, 3128, 3127, 3126, 3125, 3124, 3123, 3122, 3121, 3120, 3119, 3118,
3117, 3116, 3115, 3114, 3113, 3112, 3111, 3110, 3109, 3108, 3107, 3106,
3105, 3104, 3103, 3102, 3101, 3100, 3099, 3098, 3097, 3096, 3095, 3094,
3093, 3092, 3091, 3090, 3089, 3088, 3087, 3086, 3085, 3084, 3083, 3082,
3081, 3080, 3079, 3078, 3077, 3076, 3075, 3074, 3073, 3072, 3071, 3070,
3069, 3068, 3067, 3066, 3065, 3064, 3063, 3062, 3061, 3060, 3059, 3058,
3057, 3056, 3055, 3054, 3053, 3052, 3051, 3050, 3049, 3048, 3047, 3046,
3045, 3044, 3043, 3042, 3041, 3040, 3039, 3038, 3037, 3036, 3035, 3034,
3033, 3032, 3031, 3030, 3029, 3028, 3027, 3026, 3025, 3024, 3023, 3022,
3021, 3020, 3019, 3018, 3017, 3016, 3015, 3014, 3013, 3012, 3011, 3010,
3009, 3008, 3007, 3006, 3005, 3004, 3003, 3002, 3001, 3000, 2999, 2998,
2997, 2996, 2995, 2994, 2993, 2992, 2991, 2990, 2989, 2988, 2987, 2986,
2985, 2984, 2983, 2982, 2981, 2980, 2979, 2978, 2977, 2976, 2975, 2974,
2973, 2972, 2971, 2970, 2969, 2968, 2967, 2966, 2965, 2964, 2963, 2962,
2961, 2960, 2959, 2958, 2957, 2956, 2955, 2954, 2953, 2952, 2951, 2950,
2949, 2948, 2947, 2946, 2945, 2944, 2943, 2942, 2941, 2940, 2939, 2938,
2937, 2936, 2935, 2934, 2933, 2932, 2931, 2930, 2929, 2928, 2927, 2926,
2925, 2924, 2923, 2922, 2921, 2920, 2919, 2918, 2917, 2916, 2915, 2914,
2913, 2912, 2911, 2910, 2909, 2908, 2907, 2906, 2905, 2904, 2903, 2902,
2901, 2900, 2899, 2898, 2897, 2896, 2895, 2894, 2893, 2892, 2891, 2890,
2889, 2888, 2887, 2886, 2885, 2884, 2883, 2882, 2881, 2880, 2879, 2878,
2877, 2876, 2875, 2874, 2873, 2872, 2871, 2870, 2869, 2868, 2867, 2866,
2865, 2864, 2863, 2862, 2861, 2860, 2859, 2858, 2857, 2856, 2855, 2854,
2853, 2852, 2851, 2850, 2849, 2848, 2847, 2846, 2845, 2844, 2843, 2842,
2841, 2840, 2839, 2838, 2837, 2836, 2835, 2834, 2833, 2832, 2831, 2830,
2829, 2828, 2827, 2826, 2825, 2824, 2823, 2822, 2821, 2820, 2819, 2818,
2817, 2816, 2815, 2814, 2813, 2812, 2811, 2810, 2809, 2808, 2807, 2806,
2805, 2804, 2803, 2802, 2801, 2800, 2799, 2798, 2797, 2796, 2795, 2794,
2793, 2792, 2791, 2790, 2789, 2788, 2787, 2786, 2785, 2784, 2783, 2782,
2781, 2780, 2779, 2778, 2777, 2776, 2775, 2774, 2773, 2772, 2771, 2770,
2769, 2768, 2767, 2766, 2765, 2764, 2763, 2762, 2761, 2760, 2759, 2758,
2757, 2756, 2755, 2754, 2753, 2752, 2751, 2750, 2749, 2748, 2747, 2746,
2745, 2744, 2743, 2742, 2741, 2740, 2739, 2738, 2737, 2736, 2735, 2734,
2733, 2732, 2731, 2730, 2729, 2728, 2727, 2726, 2725, 2724, 2723, 2722,
2721, 2720, 2719, 2718, 2717, 2716, 2715, 2714, 2713, 2712, 2711, 2710,
2709, 2708, 2707, 2706, 2705, 2704, 2703, 2702, 2701, 2700, 2699, 2698,
3182, 3181, 3180, 3179, 3178, 3177, 50, 49, 48, 47, 22, 25 ) and p.addr
= e.addr join res_partner_address as a on a.partner_id = e.id and
(a.type = 'default' or a.type IS NULL) and (p.name != a.name or p.name2
!= a.name2) and p.addr = e.addr where p.date = e.date and e.date =
(select max(date) from res_partner as msd where msd.addr = p.addr) and
e.active = true;










             
QUERY
PLAN










   


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=0.00..4044.83 rows=1 width=726) (actual  
time=512002.518..513103.896 rows=1 loops=1)  Join Filter: (((p.addr)::text = (e.addr)::text) AND (p.date = e.date)
AND (((p.name)::text <> (a.name)::text) OR ((p.name2)::text <>
(a.name2)::text)))  ->  Nested Loop  (cost=0.00..199.35 rows=1 width=730) (actual
time=2.468..59.751 rows=520 loops=1)        ->  Seq Scan on res_partner_address a  (cost=0.00..88.40
rows=16 width=552) (actual time=0.851..16.131 rows=559 loops=1)              Filter: ((("type")::text =
'default'::text)OR ("type" IS  
NULL))        ->  Index Scan using res_partner_id_uniq on res_partner e
(cost=0.00..6.92 rows=1 width=186) (actual time=0.048..0.059 rows=1
loops=559)              Index Cond: (a.partner_id = e.id)              Filter: ((id = ANY

('{3214,3213,3212,3211,3210,3209,3208,3207,3206,3205,3204,3203,3202,3201,3200,3199,3198,3197,3196,3195,3194,3193,3192,3191,3190,3189,3188,3187,3186,3185,3184,3183,3176,3175,3174,3173,3172,3171,3170,3169,3168,3167,3166,3165,3164,3163,3162,3161,3160,3159,3158,3157,3156,3155,3154,3153,3152,3151,3150,3149,3148,3147,3146,3145,3144,3143,3142,3141,3140,3139,3138,3137,3136,3135,3134,3133,3132,3131,3130,3129,3128,3127,3126,3125,3124,3123,3122,3121,3120,3119,3118,3117,3116,3115,3114,3113,3112,3111,3110,3109,3108,3107,3106,3105,3104,3103,3102,3101,3100,3099,3098,3097,3096,3095,3094,3093,3092,3091,3090,3089,3088,3087,3086,3085,3084,3083,3082,3081,3080,3079,3078,3077,3076,3075,3074,3073,3072,3071,3070,3069,3068,3067,3066,3065,3064,3063,3062,3061,3060,3059,3058,3057,3056,3055,3054,3053,3052,3051,3050,3049,3048,3047,3046,3045,3044,3043,3042,3041,3040,3039,3038,3037,3036,3035,3034,3033,3032,3031,3030,3029,3028,3027,3026,3025,3024,3023,3022,3021,3020,3019,3018,3017,3016,3015,3014,3013,3012,3011,3010,3009,3008,3007,3006,3005,3004,3003,3002,3001,3000,2999,2998,2997,2996,2995,2994,2993,2992,2991,2990,2989,2988,2987,2986,2985,2984,2983,2982,2981,2980,2979,2978,2977,2976,2975,2974,2973,2972,2971,2970,2969,2968,2967,2966,2965,2964,2963,2962,2961,2960,2959,2958,2957,2956,2955,2954,2953,2952,2951,2950,2949,2948,2947,2946,2945,2944,2943,2942,2941,2940,2939,2938,2937,2936,2935,2934,2933,2932,2931,2930,2929,2928,2927,2926,2925,2924,2923,2922,2921,2920,2919,2918,2917,2916,2915,2914,2913,2912,2911,2910,2909,2908,2907,2906,2905,2904,2903,2902,2901,2900,2899,2898,2897,2896,2895,2894,2893,2892,2891,2890,2889,2888,2887,2886,2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875,2874,2873,2872,2871,2870,2869,2868,2867,2866,2865,2864,2863,2862,2861,2860,2859,2858,2857,2856,2855,2854,2853,2852,2851,2850,2849,2848,2847,2846,2845,2844,2843,2842,2841,2840,2839,2838,2837,2836,2835,2834,2833,2832,2831,2830,2829,2828,2827,2826,2825,2824,2823,2822,2821,2820,2819,2818,2817,2816,2815,2814,2813,2812,2811,2810,2809,2808,2807,2806,2805,2804,2803,2802,2801,2800,2799,2798,2797,2796,2795,2794,2793,2792,2791,2790,2789,2788,2787,2786,2785,2784,2783,2782,2781,2780,2779,2778,2777,2776,2775,2774,2773,2772,2771,2770,2769,2768,2767,2766,2765,2764,2763,2762,2761,2760,2759,2758,2757,2756,2755,2754,2753,2752,2751,2750,2749,2748,2747,2746,2745,2744,2743,2742,2741,2740,2739,2738,2737,2736,2735,2734,2733,2732,2731,2730,2729,2728,2727,2726,2725,2724,2723,2722,2721,2720,2719,2718,2717,2716,2715,2714,2713,2712,2711,2710,2709,2708,2707,2706,2705,2704,2703,2702,2701,2700,2699,2698,3182,3181,3180,3179,3178,3177,50,49,48,47,22,25}'::integer[]))

AND active)  ->  Seq Scan on myfile p  (cost=0.00..3845.46 rows=1 width=730)
(actual time=1.971..983.174 rows=510 loops=520)        Filter: ((subplan) = date)        SubPlan          ->  Aggregate
(cost=159.20..159.22 rows=1 width=4) (actual  
time=1.867..1.871 rows=1 loops=268840)                ->  Seq Scan on res_partner msd  (cost=0.00..159.16
rows=16 width=4) (actual time=1.331..1.821 rows=1 loops=268840)                      Filter: ((addr)::text =
($0)::text)Totalruntime: 513105.731 ms 
(15 rows)


=============================================================================
WITHOUT e.active = true

=# EXPLAIN ANALYZE select p.addr, p.name, p.name2 from myfile as p join
res_partner as e on e.id in ( 3214, 3213, 3212, 3211, 3210, 3209, 3208,
3207, 3206, 3205, 3204, 3203, 3202, 3201, 3200, 3199, 3198, 3197, 3196,
3195, 3194, 3193, 3192, 3191, 3190, 3189, 3188, 3187, 3186, 3185, 3184,
3183, 3176, 3175, 3174, 3173, 3172, 3171, 3170, 3169, 3168, 3167, 3166,
3165, 3164, 3163, 3162, 3161, 3160, 3159, 3158, 3157, 3156, 3155, 3154,
3153, 3152, 3151, 3150, 3149, 3148, 3147, 3146, 3145, 3144, 3143, 3142,
3141, 3140, 3139, 3138, 3137, 3136, 3135, 3134, 3133, 3132, 3131, 3130,
3129, 3128, 3127, 3126, 3125, 3124, 3123, 3122, 3121, 3120, 3119, 3118,
3117, 3116, 3115, 3114, 3113, 3112, 3111, 3110, 3109, 3108, 3107, 3106,
3105, 3104, 3103, 3102, 3101, 3100, 3099, 3098, 3097, 3096, 3095, 3094,
3093, 3092, 3091, 3090, 3089, 3088, 3087, 3086, 3085, 3084, 3083, 3082,
3081, 3080, 3079, 3078, 3077, 3076, 3075, 3074, 3073, 3072, 3071, 3070,
3069, 3068, 3067, 3066, 3065, 3064, 3063, 3062, 3061, 3060, 3059, 3058,
3057, 3056, 3055, 3054, 3053, 3052, 3051, 3050, 3049, 3048, 3047, 3046,
3045, 3044, 3043, 3042, 3041, 3040, 3039, 3038, 3037, 3036, 3035, 3034,
3033, 3032, 3031, 3030, 3029, 3028, 3027, 3026, 3025, 3024, 3023, 3022,
3021, 3020, 3019, 3018, 3017, 3016, 3015, 3014, 3013, 3012, 3011, 3010,
3009, 3008, 3007, 3006, 3005, 3004, 3003, 3002, 3001, 3000, 2999, 2998,
2997, 2996, 2995, 2994, 2993, 2992, 2991, 2990, 2989, 2988, 2987, 2986,
2985, 2984, 2983, 2982, 2981, 2980, 2979, 2978, 2977, 2976, 2975, 2974,
2973, 2972, 2971, 2970, 2969, 2968, 2967, 2966, 2965, 2964, 2963, 2962,
2961, 2960, 2959, 2958, 2957, 2956, 2955, 2954, 2953, 2952, 2951, 2950,
2949, 2948, 2947, 2946, 2945, 2944, 2943, 2942, 2941, 2940, 2939, 2938,
2937, 2936, 2935, 2934, 2933, 2932, 2931, 2930, 2929, 2928, 2927, 2926,
2925, 2924, 2923, 2922, 2921, 2920, 2919, 2918, 2917, 2916, 2915, 2914,
2913, 2912, 2911, 2910, 2909, 2908, 2907, 2906, 2905, 2904, 2903, 2902,
2901, 2900, 2899, 2898, 2897, 2896, 2895, 2894, 2893, 2892, 2891, 2890,
2889, 2888, 2887, 2886, 2885, 2884, 2883, 2882, 2881, 2880, 2879, 2878,
2877, 2876, 2875, 2874, 2873, 2872, 2871, 2870, 2869, 2868, 2867, 2866,
2865, 2864, 2863, 2862, 2861, 2860, 2859, 2858, 2857, 2856, 2855, 2854,
2853, 2852, 2851, 2850, 2849, 2848, 2847, 2846, 2845, 2844, 2843, 2842,
2841, 2840, 2839, 2838, 2837, 2836, 2835, 2834, 2833, 2832, 2831, 2830,
2829, 2828, 2827, 2826, 2825, 2824, 2823, 2822, 2821, 2820, 2819, 2818,
2817, 2816, 2815, 2814, 2813, 2812, 2811, 2810, 2809, 2808, 2807, 2806,
2805, 2804, 2803, 2802, 2801, 2800, 2799, 2798, 2797, 2796, 2795, 2794,
2793, 2792, 2791, 2790, 2789, 2788, 2787, 2786, 2785, 2784, 2783, 2782,
2781, 2780, 2779, 2778, 2777, 2776, 2775, 2774, 2773, 2772, 2771, 2770,
2769, 2768, 2767, 2766, 2765, 2764, 2763, 2762, 2761, 2760, 2759, 2758,
2757, 2756, 2755, 2754, 2753, 2752, 2751, 2750, 2749, 2748, 2747, 2746,
2745, 2744, 2743, 2742, 2741, 2740, 2739, 2738, 2737, 2736, 2735, 2734,
2733, 2732, 2731, 2730, 2729, 2728, 2727, 2726, 2725, 2724, 2723, 2722,
2721, 2720, 2719, 2718, 2717, 2716, 2715, 2714, 2713, 2712, 2711, 2710,
2709, 2708, 2707, 2706, 2705, 2704, 2703, 2702, 2701, 2700, 2699, 2698,
3182, 3181, 3180, 3179, 3178, 3177, 50, 49, 48, 47, 22, 25 ) and p.addr
= e.addr join res_partner_address as a on a.partner_id = e.id and
(a.type = 'default' or a.type IS NULL) and (p.name != a.name or p.name2
!= a.name2) and p.addr = e.addr where p.date = e.date and e.date =
(select max(date) from res_partner as msd where msd.addr = p.addr);










                                                          
QUERY
PLAN









                                                                                                                     


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=0.00..4044.85 rows=1 width=726) (actual  
time=11786.432..13216.290 rows=1 loops=1)  Join Filter: (((p.addr)::text = (e.addr)::text) AND (p.date = e.date)
AND (((p.name)::text <> (a.name)::text) OR ((p.name2)::text <>
(a.name2)::text)))  ->  Seq Scan on myfile p  (cost=0.00..3845.46 rows=1 width=730)
(actual time=2.347..993.933 rows=510 loops=1)        Filter: ((subplan) = date)        SubPlan          ->  Aggregate
(cost=159.20..159.22rows=1 width=4) (actual  
time=1.884..1.886 rows=1 loops=517)                ->  Seq Scan on res_partner msd  (cost=0.00..159.16
rows=16 width=4) (actual time=1.337..1.838 rows=1 loops=517)                      Filter: ((addr)::text = ($0)::text)
-> Nested Loop  (cost=0.00..199.35 rows=2 width=730) (actual  
time=1.357..22.270 rows=522 loops=510)        ->  Seq Scan on res_partner_address a  (cost=0.00..88.40
rows=16 width=552) (actual time=0.106..3.521 rows=559 loops=510)              Filter: ((("type")::text =
'default'::text)OR ("type" IS  
NULL))        ->  Index Scan using res_partner_id_uniq on res_partner e
(cost=0.00..6.92 rows=1 width=186) (actual time=0.017..0.021 rows=1
loops=285090)              Index Cond: (a.partner_id = e.id)              Filter: (id = ANY

('{3214,3213,3212,3211,3210,3209,3208,3207,3206,3205,3204,3203,3202,3201,3200,3199,3198,3197,3196,3195,3194,3193,3192,3191,3190,3189,3188,3187,3186,3185,3184,3183,3176,3175,3174,3173,3172,3171,3170,3169,3168,3167,3166,3165,3164,3163,3162,3161,3160,3159,3158,3157,3156,3155,3154,3153,3152,3151,3150,3149,3148,3147,3146,3145,3144,3143,3142,3141,3140,3139,3138,3137,3136,3135,3134,3133,3132,3131,3130,3129,3128,3127,3126,3125,3124,3123,3122,3121,3120,3119,3118,3117,3116,3115,3114,3113,3112,3111,3110,3109,3108,3107,3106,3105,3104,3103,3102,3101,3100,3099,3098,3097,3096,3095,3094,3093,3092,3091,3090,3089,3088,3087,3086,3085,3084,3083,3082,3081,3080,3079,3078,3077,3076,3075,3074,3073,3072,3071,3070,3069,3068,3067,3066,3065,3064,3063,3062,3061,3060,3059,3058,3057,3056,3055,3054,3053,3052,3051,3050,3049,3048,3047,3046,3045,3044,3043,3042,3041,3040,3039,3038,3037,3036,3035,3034,3033,3032,3031,3030,3029,3028,3027,3026,3025,3024,3023,3022,3021,3020,3019,3018,3017,3016,3015,3014,3013,3012,3011,3010,3009,3008,3007,3006,3005,3004,3003,3002,3001,3000,2999,2998,2997,2996,2995,2994,2993,2992,2991,2990,2989,2988,2987,2986,2985,2984,2983,2982,2981,2980,2979,2978,2977,2976,2975,2974,2973,2972,2971,2970,2969,2968,2967,2966,2965,2964,2963,2962,2961,2960,2959,2958,2957,2956,2955,2954,2953,2952,2951,2950,2949,2948,2947,2946,2945,2944,2943,2942,2941,2940,2939,2938,2937,2936,2935,2934,2933,2932,2931,2930,2929,2928,2927,2926,2925,2924,2923,2922,2921,2920,2919,2918,2917,2916,2915,2914,2913,2912,2911,2910,2909,2908,2907,2906,2905,2904,2903,2902,2901,2900,2899,2898,2897,2896,2895,2894,2893,2892,2891,2890,2889,2888,2887,2886,2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875,2874,2873,2872,2871,2870,2869,2868,2867,2866,2865,2864,2863,2862,2861,2860,2859,2858,2857,2856,2855,2854,2853,2852,2851,2850,2849,2848,2847,2846,2845,2844,2843,2842,2841,2840,2839,2838,2837,2836,2835,2834,2833,2832,2831,2830,2829,2828,2827,2826,2825,2824,2823,2822,2821,2820,2819,2818,2817,2816,2815,2814,2813,2812,2811,2810,2809,2808,2807,2806,2805,2804,2803,2802,2801,2800,2799,2798,2797,2796,2795,2794,2793,2792,2791,2790,2789,2788,2787,2786,2785,2784,2783,2782,2781,2780,2779,2778,2777,2776,2775,2774,2773,2772,2771,2770,2769,2768,2767,2766,2765,2764,2763,2762,2761,2760,2759,2758,2757,2756,2755,2754,2753,2752,2751,2750,2749,2748,2747,2746,2745,2744,2743,2742,2741,2740,2739,2738,2737,2736,2735,2734,2733,2732,2731,2730,2729,2728,2727,2726,2725,2724,2723,2722,2721,2720,2719,2718,2717,2716,2715,2714,2713,2712,2711,2710,2709,2708,2707,2706,2705,2704,2703,2702,2701,2700,2699,2698,3182,3181,3180,3179,3178,3177,50,49,48,47,22,25}'::integer[]))Total
runtime:13217.118 ms 
(15 rows)