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 BF13CD1C9EA for ; Mon, 4 Aug 2003 14:10: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 14442-08 for ; Mon, 4 Aug 2003 11:09:04 -0300 (ADT) Received: from email03.aon.at (WARSL402PIP6.highway.telekom.at [195.3.96.93]) by svr1.postgresql.org (Postfix) with SMTP id 6550AD1C4BD for ; Mon, 4 Aug 2003 11:10:05 -0300 (ADT) Received: (qmail 378542 invoked from network); 4 Aug 2003 14:10:08 -0000 Received: from m154p007.dipool.highway.telekom.at (HELO cantor) ([62.46.9.39]) (envelope-sender ) by qmail3rs.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 4 Aug 2003 14:10:08 -0000 From: Manfred Koizar To: "Fernando Papa" Cc: Subject: Re: I can't wait too much: Total runtime 432478.44 msec Date: Mon, 04 Aug 2003 16:10:18 +0200 Message-ID: References: In-Reply-To: X-Mailer: Forte Agent 1.93/32.576 English (American) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/25 X-Sequence-Number: 2842 On Fri, 1 Aug 2003 18:17:17 -0300, "Fernando Papa" wrote: > AND cont_publicacion.fecha_publicacion = (SELECT >max(cp1.fecha_publicacion) > FROM cont_publicacion cp1 > WHERE cp1.id_instalacion = >cont_publicacion.id_instalacion > AND cp1.id_contenido = cont_publicacion.id_contenido > > AND cp1.generar_vainilla = >cont_publicacion.generar_vainilla) If certain uniqueness conditions are met, the Postgres specific DISTINCT ON clause could help totally eliminating the subselect: SELECT DISTINCT ON ( cp.id_instalacion, cp.id_contenido, cp.generar_vainilla, cp.fecha_publicacion ) cc.id_contenido ,cc.pertenece_premium ,cc.Titulo_esp as v_sufix ,cc.url_contenido ,cc.tipo_acceso ,cc.id_sbc ,cc.cant_vistos ,cc.cant_votos ,cc.puntaje_total ,cc.id_contenido_padre ,jc.imagen_tapa_especial ,jc.info_general_esp as info_general ,jc.ayuda ,jc.tips_tricks_esp as tips_tricks ,jc.mod_imagen_tapa_especial ,cp.fecha_publicacion as fecha_publicacion ,cp.generar_Vainilla FROM cont_contenido cc ,juegos_config jc ,cont_publicacion cp WHERE cc.id_instalacion = 2 AND cc.id_sbc = 619 AND cc.id_tipo = 2 AND cc.id_instalacion = jc.id_instalacion AND cc.id_contenido = jc.id_contenido AND upper(cp.generar_Vainilla) = 'S' AND cp.id_instalacion = cc.id_instalacion AND cp.id_contenido = cc.id_contenido ORDER BY cp.id_instalacion, cp.id_contenido, cp.generar_vainilla, cp.fecha_publicacion desc However, this doesn't get the result in the original order, so you have to wrap another SELECT ... ORDER BY ... LIMIT around it. Or try to move the subselect into the FROM clause: SELECT cc.id_contenido ,cc.pertenece_premium ,cc.Titulo_esp as v_sufix ,cc.url_contenido ,cc.tipo_acceso ,cc.id_sbc ,cc.cant_vistos ,cc.cant_votos ,cc.puntaje_total ,cc.id_contenido_padre ,jc.imagen_tapa_especial ,jc.info_general_esp as info_general ,jc.ayuda ,jc.tips_tricks_esp as tips_tricks ,jc.mod_imagen_tapa_especial ,cp.fecha_publicacion as fecha_publicacion ,cp.generar_Vainilla FROM cont_contenido cc ,juegos_config jc ,(SELECT DISTINCT ON ( id_instalacion, id_contenido, generar_vainilla, fecha_publicacion ) * FROM cont_publicacion ORDER BY id_instalacion, id_contenido, generar_vainilla, fecha_publicacion desc ) AS cp WHERE cc.id_instalacion = 2 AND cc.id_sbc = 619 AND cc.id_tipo = 2 AND cc.id_instalacion = jc.id_instalacion AND cc.id_contenido = jc.id_contenido AND upper(cp.generar_Vainilla) = 'S' AND cp.id_instalacion = cc.id_instalacion AND cp.id_contenido = cc.id_contenido ORDER BY cp.fecha_publicacion desc LIMIT 10 OFFSET 0 [completely untested] Servus Manfred