Re: 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(at)verizon(dot)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(at)verizon(dot)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(at)verizon(dot)net> wrote:
> Pavel Stehule wrote:
> > On 11/12/2007, Gerry Reno <greno(at)verizon(dot)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)
Total runtime: 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.22 rows=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(at)verizon(dot)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(at)verizon(dot)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