X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 584713A4CBE for ; Fri, 21 Jan 2005 20:39:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05067-04 for ; Fri, 21 Jan 2005 20:39:36 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by svr1.postgresql.org (Postfix) with ESMTP id E084E3A4CC6 for ; Fri, 21 Jan 2005 20:38:28 +0000 (GMT) Received: by flake.decibel.org (Postfix, from userid 1001) id 27A811C8B3; Fri, 21 Jan 2005 14:38:27 -0600 (CST) Date: Fri, 21 Jan 2005 14:38:27 -0600 From: "Jim C. Nasby" To: pgsql-performance@postgresql.org Subject: Odd number of rows expected Message-ID: <20050121203827.GW67721@decibel.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Operating-System: FreeBSD 4.10-RELEASE-p3 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/400 X-Sequence-Number: 10090 I have a query that thinks it's going to generate a huge number of rows, when in fact it won't: INSERT INTO page_log.rrs ( bucket_id, page_id,project_id,other, hits,min_hits,max_hits,total_duration,min_duration,max_duration ) SELECT a.rrs_bucket_id, page_id,project_id,other , count(*),count(*),count(*),sum(duration),min(duration),max(duration) FROM (SELECT b.bucket_id AS rrs_bucket_id, s.* FROM rrs.bucket b JOIN page_log.log s ON ( b.prev_end_time < log_time AND b.end_time >= log_time ) WHERE b.rrs_id = '1' AND b.end_time <= '2005-01-21 20:23:00+00' AND b.end_time > '1970-01-01 00:00:00+00' ) a GROUP BY rrs_bucket_id, page_id,project_id,other; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan "*SELECT*" (cost=170461360504.98..183419912556.69 rows=91175544 width=77) -> GroupAggregate (cost=170461360504.98..183418316984.67 rows=91175544 width=29) -> Sort (cost=170461360504.98..171639141309.21 rows=471112321692 width=29) Sort Key: b.bucket_id, s.page_id, s.project_id, s.other -> Nested Loop (cost=0.00..17287707964.10 rows=471112321692 width=29) -> Seq Scan on bucket b (cost=0.00..9275.84 rows=281406 width=20) Filter: ((rrs_id = 1) AND (end_time <= '2005-01-21 20:23:00+00'::timestamp with time zone) AND (end_time > '1970-01-01 00:00:00+00'::timestamp with time zone)) -> Index Scan using log__log_time on log s (cost=0.00..36321.24 rows=1674137 width=33) Index Cond: (("outer".prev_end_time < s.log_time) AND ("outer".end_time >= s.log_time)) The final rowcount after the aggregate will actually be about 1.9M rows: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan "*SELECT*" (cost=170461360504.98..183419912556.69 rows=91175544 width=77) (actual time=156777.374..234613.843 rows=1945123 loops=1) -> GroupAggregate (cost=170461360504.98..183418316984.67 rows=91175544 width=29) (actual time=156777.345..214246.751 rows=1945123 loops=1) -> Sort (cost=170461360504.98..171639141309.21 rows=471112321692 width=29) (actual time=156777.296..177517.663 rows=4915567 loops=1) Sort Key: b.bucket_id, s.page_id, s.project_id, s.other -> Nested Loop (cost=0.00..17287707964.10 rows=471112321692 width=29) (actual time=0.662..90702.755 rows=4915567 loops=1) -> Seq Scan on bucket b (cost=0.00..9275.84 rows=281406 width=20) (actual time=0.063..1591.591 rows=265122 loops=1) Filter: ((rrs_id = 1) AND (end_time <= '2005-01-21 20:23:00+00'::timestamp with time zone) AND (end_time > '1970-01-01 00:00:00+00'::timestamp with time zone)) -> Index Scan using log__log_time on log s (cost=0.00..36321.24 rows=1674137 width=33) (actual time=0.014..0.174 rows=19 loops=265122) Index Cond: (("outer".prev_end_time < s.log_time) AND ("outer".end_time >= s.log_time)) Total runtime: 299623.954 ms Everything is analyzed, and the statistics target is set to 1000. Basically, it seems that it doesn't understand that each row in log will match up with at most one row in bucket. There is a unique index on bucket(rrs_id, end_time), so it should be able to tell this. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"