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 A270ED1CA0F for ; Mon, 4 Aug 2003 22:30:58 +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 52476-09 for ; Mon, 4 Aug 2003 19:29:44 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 6F748D1CA0E for ; Mon, 4 Aug 2003 19:30:47 -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 h74MUlSa013406; Mon, 4 Aug 2003 18:30:47 -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 19:01:50 -0300" Date: Mon, 04 Aug 2003 18:30:47 -0400 Message-ID: <13405.1060036247@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/47 X-Sequence-Number: 2864 "Fernando Papa" writes: > Thanks Tom. I vaccumed full every night. Now I drop function index and > change the upper. Nothing change (I know, total time rise because we are > doing other things on database now). > -> Seq Scan on cont_publicacion > (cost=0.00..5329.47 rows=10 width=35) (actual time=41.74..18004.75 > rows=97 loops=40) > Filter: (((generar_vainilla = 'S'::character > varying) OR (generar_vainilla = 's'::character varying)) AND > (fecha_publicacion = (subplan))) > SubPlan > -> Aggregate (cost=11.86..11.86 rows=1 > width=8) (actual time=40.15..40.15 rows=1 loops=17880) Something fishy going on here. Why did it switch to a seqscan, considering it still (mistakenly) thinks there are only going to be 10 or 20 rows matching the generar_vainilla condition? How many rows have generar_vainilla equal to 's' or 'S', anyway? In any case, the real problem is to get rid of the subselect at the Now that I look at your original query, I see that what you really seem to be after is the publications with latest pub date among each group with identical id_instalacion, id_contenido, and generar_vainilla. You would probably do well to reorganize the query using SELECT DISTINCT ON, viz SELECT * FROM (SELECT DISTINCT ON (id_instalacion, id_contenido, generar_vainilla) ... FROM ... WHERE ... ORDER BY id_instalacion, id_contenido, generar_vainilla, fecha_publicacion DESC) AS ss ORDER BY fecha_publicacion desc LIMIT 10 OFFSET 0 See the "weather reports" example in the SELECT reference page for motivation. regards, tom lane