Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C987B2E006B for ; Sun, 9 Dec 2007 22:16:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 17651-02 for ; Sun, 9 Dec 2007 22:16:39 -0400 (AST) Received: from vms173003pub.verizon.net (vms173003pub.verizon.net [206.46.173.3]) by postgresql.org (Postfix) with ESMTP id 1E2A72E005A for ; Sun, 9 Dec 2007 22:16:44 -0400 (AST) Received: from [192.168.2.102] ([71.100.89.137]) by vms173003.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JST00C2TA8RU402@vms173003.mailsrvcs.net> for pgsql-sql@postgresql.org; Sun, 09 Dec 2007 20:14:52 -0600 (CST) Date: Sun, 09 Dec 2007 21:16:32 -0500 From: Gerry Reno Subject: Re: join on three tables is slow In-reply-to: <475C96EA.6090002@verizon.net> To: pgsql-sql@postgresql.org Message-id: <475CA180.7070004@verizon.net> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: quoted-printable References: <475C96EA.6090002@verizon.net> User-Agent: Thunderbird 1.5.0.12 (X11/20070530) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.195 tagged_above=0 required=5 tests=AWL=2.305, BAYES_05=-1.11 X-Spam-Level: * X-Archive-Number: 200712/28 X-Sequence-Number: 29720 Here is the query plan: QUERYPLAN = = = = = = = = = = = = = = = = = = =20 2698, 3182, 3181,=20 31-----------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= ----------------------------------------- Nested Loop (cost=3D0.00..4043.53 rows=3D1 width=3D726) Join Filter: (((p.addr)::text =3D (e.addr)::text) AND (p.date =3D e.da= te)=20 AND (((p.name)::text <> (a.name)::text) OR ((p.name2)::text <>=20 (a.name2)::text))) -> Nested Loop (cost=3D0.00..198.05 rows=3D1 width=3D730) -> Seq Scan on res_partner_address a (cost=3D0.00..87.10=20 rows=3D16 width=3D552) Filter: ((("type")::text =3D 'default'::text) OR ("type" I= S=20 NULL)) -> Index Scan using res_partner_id_uniq on res_partner e =20 (cost=3D0.00..6.92 rows=3D1 width=3D186) Index Cond: (a.partner_id =3D e.id) Filter: ((id =3D ANY=20 ('{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,318= 6,3185,3184,3183,3176,3175,3174,3173,3172,3171,3170,3169,3168,3167,3166,3= 165,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,31= 36,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,310= 7,3106,3105,3104,3103,3102,3101,3100,3099,3098,3097,3096,3095,3094,3093,3= 092,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,30= 63,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,303= 4,3033,3032,3031,3030,3029,3028,3027,3026,3025,3024,3023,3022,3021,3020,3= 019,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,29= 90,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,296= 1,2960,2959,2958,2957,2956,2955,2954,2953,2952,2951,2950,2949,2948,2947,2= 946,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,29= 17,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,288= 8,2887,2886,2885,2884,2883,2882,2881,2880,2879,2878,2877,2876,2875,2874,2= 873,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,28= 44,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,281= 5,2814,2813,2812,2811,2810,2809,2808,2807,2806,2805,2804,2803,2802,2801,2= 800,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,27= 71,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,274= 2,2741,2740,2739,2738,2737,2736,2735,2734,2733,2732,2731,2730,2729,2728,2= 727,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,26= 98,3182,3181,3180,3179,3178,3177,50,49,48,47,22,25}'::integer[]))=20 AND active) -> Seq Scan on myfile p (cost=3D0.00..3845.46 rows=3D1 width=3D730) Filter: ((subplan) =3D date) SubPlan -> Aggregate (cost=3D159.20..159.22 rows=3D1 width=3D4) -> Seq Scan on res_partner msd (cost=3D0.00..159.16=20 rows=3D16 width=3D4) Filter: ((addr)::text =3D ($0)::text) (14 rows) Gerry