From: Tom Lane <tgl@sss.pgh.pa.us>
To: Fernando Papa <fpapa@claxson.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: I can't wait too much: Total runtime 432478.44 msec
Date: Mon, 04 Aug 2003 17:28:00 -0400
Message-ID: <12726.1060032480@sss.pgh.pa.us> (raw)
In-Reply-To: <F1DC5B511E2D1C499E5E20FC6D74160D03A04E6A@exch2000.buehuergo.corp.claxson.com>
References: <F1DC5B511E2D1C499E5E20FC6D74160D03A04E6A@exch2000.buehuergo.corp.claxson.com>
"Fernando Papa" <fpapa@claxson.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, fpapa@claxson.com
Subject: Re: I can't wait too much: Total runtime 432478.44 msec
In-Reply-To: <12726.1060032480@sss.pgh.pa.us>
References: <F1DC5B511E2D1C499E5E20FC6D74160D03A04E6A@exch2000.buehuergo.corp.claxson.com> <12726.1060032480@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox