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

From Gerry Reno
Subject join on three tables is slow
Date
Msg-id 475C96EA.6090002@verizon.net
Whole thread Raw
Responses Re: join on three tables is slow
Re: join on three tables is slow
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Steve Midgley
Date:
Subject: Re: INSERT INTO relational tables
Next
From: Gerry Reno
Date:
Subject: Re: join on three tables is slow