Обсуждение: join on three tables is slow
I have a join that uses three tables but it runs rather slow. For example, the following command takes about 10 min. to run. It gets the correct result but what should I do to increase the performance of this query? This query is the end result of some python code hence the big id list. myfile has 600 records, res_partner has 600 records, res_partner_address has 1000 records 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 ) 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 e.active = '1' and p.date = e.date and e.date = (select max(date) from res_partner as msd where msd.addr = p.addr) Thanks, Gerry
Here is the query plan:
QUERYPLAN
2698, 3182, 3181,
31---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested
Loop (cost=0.00..4043.53 rows=1 width=726) 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..198.05 rows=1 width=730) -> Seq Scan on res_partner_address a
(cost=0.00..87.10
rows=16 width=552) 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) 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) Filter: ((subplan) = date)
SubPlan -> Aggregate (cost=159.20..159.22 rows=1 width=4) -> Seq Scan on res_partner msd
(cost=0.00..159.16
rows=16 width=4) Filter: ((addr)::text = ($0)::text)
(14 rows)
Gerry
Gerry Reno wrote: > I have a join that uses three tables but it runs rather slow. For > example, the following command takes about 10 min. to run. It gets the > correct result but what should I do to increase the performance of this > query? This query is the end result of some python code hence the big > id list. > > myfile has 600 records, res_partner has 600 records, res_partner_address > has 1000 records > > 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, snip > 3179, 3178, 3177, 50, 49, 48, 47, 22, 25 ) 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 > e.active = '1' and p.date = e.date and e.date = (select max(date) from > res_partner as msd where msd.addr = p.addr) > To start with - You have join res_partner as e on e.id in (... big list...) That list should be the contents of a where clause not a join. You want that first part to be join res_partner as e on e.id=p.something So as a first step that join will link all 523 res_partner rows listed with every myfile row - that means you will get 313,800 rows from this join with your other joins and where clause then trim that down to the final result. I would also say that the rest of your joins don't appear to be what you really want. (but part of them may belong in the where clause) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Ok, I've been playing around with this SQL some more and I found that if I remove this: e.active = '1' from the query that the query now completes in 5 seconds. Nothing else has anywhere near the impact of this boolean condition. So what is it about this boolean field that is causing so much delay? Gerry
On 11/12/2007, Gerry Reno <greno@verizon.net> wrote: > Ok, I've been playing around with this SQL some more and I found that if > I remove this: > e.active = '1' > from the query that the query now completes in 5 seconds. Nothing else > has anywhere near the impact of this boolean condition. So what is it > about this boolean field that is causing so much delay? > send execution plan and we can see. Maybe you need a cast on every row, because '1' is varchar. Try e.active = true Pavel > Gerry > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >
Pavel Stehule wrote: > On 11/12/2007, Gerry Reno <greno@verizon.net> wrote: > >> Ok, I've been playing around with this SQL some more and I found that if >> I remove this: >> e.active = '1' >> from the query that the query now completes in 5 seconds. Nothing else >> has anywhere near the impact of this boolean condition. So what is it >> about this boolean field that is causing so much delay? >> >> > > send execution plan and we can see. Maybe you need a cast on every > row, because '1' is varchar. Try e.active = true > Tried e.active = true and no difference. > Pavel > > >> Gerry >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 7: You can help support the PostgreSQL project by donating at >> >> http://www.postgresql.org/about/donate >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > Here is the query plan with e.active = true in place: QUERY PLAN on a.partner_id = e.---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.00..4044.83 rows=1 width=726) 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) -> Seq Scan on res_partner_address a (cost=0.00..88.40 rows=16 width=552) 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) 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) Filter: ((subplan) = date) SubPlan -> Aggregate (cost=159.20..159.22 rows=1 width=4) -> Seq Scan on res_partner msd (cost=0.00..159.16 rows=16 width=4) Filter: ((addr)::text = ($0)::text) (14 rows) ============================================================= And here is the query plan without e.active = true: QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.00..4044.85 rows=1 width=726) 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) Filter: ((subplan) = date) SubPlan -> Aggregate (cost=159.20..159.22 rows=1 width=4) -> Seq Scan on res_partner msd (cost=0.00..159.16 rows=16 width=4) Filter: ((addr)::text = ($0)::text) -> Nested Loop (cost=0.00..199.35 rows=2 width=730) -> Seq Scan on res_partner_address a (cost=0.00..88.40 rows=16 width=552) 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) 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[])) (14 rows)
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 \n 11/12/2007, Gerry Reno <greno@verizon.net> wrote: > Pavel Stehule wrote: > > On 11/12/2007, Gerry Reno <greno@verizon.net> wrote: > > > >> Ok, I've been playing around with this SQL some more and I found that if > >> I remove this: > >> e.active = '1' > >> from the query that the query now completes in 5 seconds. Nothing else > >> has anywhere near the impact of this boolean condition. So what is it > >> about this boolean field that is causing so much delay? > >> > >> > > > > send execution plan and we can see. Maybe you need a cast on every > > row, because '1' is varchar. Try e.active = true > > > Tried e.active = true and no difference. > > > Pavel > > > > > >> Gerry > >> > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 7: You can help support the PostgreSQL project by donating at > >> > >> http://www.postgresql.org/about/donate > >> > >> > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > > Here is the query plan with e.active = true in place: > > QUERY > PLAN > on a.partner_id = > e.--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Nested Loop (cost=0.00..4044.83 rows=1 width=726) > 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) > -> Seq Scan on res_partner_address a (cost=0.00..88.40 > rows=16 width=552) > 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) > 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) > Filter: ((subplan) = date) > SubPlan > -> Aggregate (cost=159.20..159.22 rows=1 width=4) > -> Seq Scan on res_partner msd (cost=0.00..159.16 > rows=16 width=4) > Filter: ((addr)::text = ($0)::text) > (14 rows) > > ============================================================= > > And here is the query plan without e.active = true: > > QUERY > PLAN > > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Nested Loop (cost=0.00..4044.85 rows=1 width=726) > 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) > Filter: ((subplan) = date) > SubPlan > -> Aggregate (cost=159.20..159.22 rows=1 width=4) > -> Seq Scan on res_partner msd (cost=0.00..159.16 > rows=16 width=4) > Filter: ((addr)::text = ($0)::text) > -> Nested Loop (cost=0.00..199.35 rows=2 width=730) > -> Seq Scan on res_partner_address a (cost=0.00..88.40 > rows=16 width=552) > 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) > 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[])) > (14 rows) > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
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)
Hello
1) increase statistics on res_partner_address.type (about 100)
alter table ...ALTER [ COLUMN ] column SET STATISTICS integer
do analyze, and look again on plans. There are difference
-> 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))
2) I thing so index on res_partner.addr can help or use better column
for identification (varchar isn't best) and partial index
rep_partner_address.type is 'default' or resp_partner_address.type is null.
Regards
Pavel Stehule
Gerry Reno <greno@verizon.net> writes:
> Pavel Stehule wrote:
>> there is diference in agg position. Send, please, query and explain
>> analyze output.
[ explain analyze output ]
The rowcount estimates seem pretty far off, even for simple cases that
I'd expect it to get right, eg
> -> 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))
Are the ANALYZE stats up to date for these tables?
What PG version is this, anyway?
regards, tom lane
Tom Lane wrote:
> Gerry Reno <greno@verizon.net> writes:
>
>> Pavel Stehule wrote:
>>
>>> there is diference in agg position. Send, please, query and explain
>>> analyze output.
>>>
>
> [ explain analyze output ]
>
> The rowcount estimates seem pretty far off, even for simple cases that
> I'd expect it to get right, eg
>
>
>> -> 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))
>>
>
> Are the ANALYZE stats up to date for these tables?
>
> What PG version is this, anyway?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
-bash-3.2$ yum list postgresql
Loading "installonlyn" plugin
Installed Packages
postgresql.i386 8.2.4-1.fc7
installed
You were right on the money Tom. I vacuumed the database and now the
query with the boolean executes in only 50% more time than without.
About 15 secs instead of 10 secs. Big improvement. I hadn't seen a
vacuum produce this much of an improvement in performance before.
Thanks,
Gerry