X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C2911D1CA47 for ; Mon, 4 Aug 2003 21:28:16 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 52323-01 for ; Mon, 4 Aug 2003 18:27:01 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 8A6AFD1CA16 for ; Mon, 4 Aug 2003 18:28:02 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h74LS0Sa012727; Mon, 4 Aug 2003 17:28:01 -0400 (EDT) To: "Fernando Papa" Cc: pgsql-performance@postgresql.org Subject: Re: I can't wait too much: Total runtime 432478.44 msec In-reply-to: References: Comments: In-reply-to "Fernando Papa" message dated "Mon, 04 Aug 2003 17:17:36 -0300" Date: Mon, 04 Aug 2003 17:28:00 -0400 Message-ID: <12726.1060032480@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/45 X-Sequence-Number: 2862 "Fernando Papa" writes: > -> Nested Loop (cost=0.00..1828.46 rows=1 width=367) > (actual time=7525.51..436843.27 rows=40 loops=1) > Join Filter: (("inner".id_contenido = > "outer".id_contenido) AND ("inner".id_instalacion = > "outer".id_instalacion)) > -> Index Scan using jue_conf_pk on juegos_config > (cost=0.00..12.19 rows=40 width=332) (actual time=0.38..6.63 rows=40 > loops=1) > -> Index Scan using idx_generar_vainilla_ci on > cont_publicacion (cost=0.00..45.39 rows=1 width=35) (actual > time=48.81..10917.53 rows=97 loops=40) > Index Cond: (upper((generar_vainilla)::text) > = 'S'::text) > Filter: (subplan) > SubPlan > -> Aggregate (cost=15.85..15.85 rows=1 > width=8) (actual time=24.30..24.30 rows=0 loops=17880) As best I can tell, the problem here is coming from a drastic underestimate of the number of rows selected by "upper(generar_vainilla) = 'S'". Evidently there are about 450 such rows (since in 40 repetitions of the inner index scan, the aggregate subplan gets evaluated 17880 times), but the planner seems to think there will be only about two such rows. Had it made a more correct estimate, it would never have picked a plan that required multiple repetitions of the indexscan. One thing I'm wondering is if you've VACUUM ANALYZEd cont_publicacion lately --- the cost estimate seems on the small side, and I'm wondering if the planner thinks the table is much smaller than it really is. But assuming you didn't make that mistake, the only solution I can see is to not use a functional index. The planner is not good about making row count estimates for functional indexes. You could replace the index on upper(generar_vainilla) with a plain index on generar_vainilla, and change the query condition from "upper(generar_vainilla) = 'S'" to "generar_vainilla IN ('S', 's')". I think the planner would have a lot better chance at understanding the statistics that way. regards, tom lane