Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id CA8069FBA33 for ; Thu, 3 May 2007 03:14:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 18758-09 for ; Thu, 3 May 2007 03:14:37 -0300 (ADT) X-Greylist: delayed 00:06:40.658478 by SQLgrey-1.7.5 Received: from web52308.mail.re2.yahoo.com (web52308.mail.re2.yahoo.com [206.190.48.151]) by postgresql.org (Postfix) with SMTP id EDA8E9FBA83 for ; Thu, 3 May 2007 03:14:38 -0300 (ADT) Received: (qmail 5750 invoked by uid 60001); 3 May 2007 06:07:57 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=cYtLj1CsOQvy4cbz8sjCCHPWjfuLLkQeEV0IKUOKUVmf+dvMove06WbvZrupyqAbNpRTRicOt9+kn6FxYqT1hU8tUr+l3GgA7QH3LCmd78LwHwXr4UnO89VaV0eWCtpDO+T88wnEGB2oeQ9x5wFat0BQ0iiZV1uejrAyfuHLY28=; X-YMail-OSG: btevt9QVM1mUZHscD3.OwkwWQNq9Et0L4W7pmsWmM5YlNngKpFx9Wn2vxoHBBmmJDw-- Received: from [202.156.12.10] by web52308.mail.re2.yahoo.com via HTTP; Wed, 02 May 2007 23:07:57 PDT X-Mailer: YahooMailRC/478 YahooMailWebService/0.7.41.10 Date: Wed, 2 May 2007 23:07:57 -0700 (PDT) From: Brian Herlihy Subject: Join vs Subquery To: Postgresql Performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1215042455-1178172477=:5712" Message-ID: <243066.5712.qm@web52308.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/16 X-Sequence-Number: 24406 --0-1215042455-1178172477=:5712 Content-Type: text/plain; charset=ascii Hi, I am using postgres 8.1.3 for this. If this has been dealt with later, please disregard. And this is not a complaint or a request, I am just curious, so I know how to best construct my queries. There is a unique index mapping domains to domain_ids. views_ts specifies a partitioned table, where views_ts_2007_04_01 is the only partition matching the range given in the query. My goal is to produce summaries of counts of rows for each day within a given range (can be days, months, years). The issue: the second query results in a lower cost estimate. I am wondering why the second query plan was not chosen for the first query. Thanks! Brian live=> explain select ts::date,count(*) from views_ts join domains using (domain_id) where domain = '1234.com' and ts >= '2007-04-01' and ts < '2007-04-02' group by ts::date; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=9040.97..9041.00 rows=2 width=8) -> Hash Join (cost=6.01..9040.96 rows=2 width=8) Hash Cond: ("outer".domain_id = "inner".domain_id) -> Append (cost=0.00..7738.01 rows=259383 width=16) -> Seq Scan on views_ts (cost=0.00..1138.50 rows=1 width=16) Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone)) -> Seq Scan on views_ts_2007_04_01 views_ts (cost=0.00..6599.51 rows=259382 width=16) Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone)) -> Hash (cost=6.01..6.01 rows=1 width=8) -> Index Scan using domains_domain on domains (cost=0.00..6.01 rows=1 width=8) Index Cond: ("domain" = '1234.com'::text) (11 rows) live=> explain select ts::date,count(*) from views_ts where domain_id = (select domain_id from domains where domain = '1234.com') and ts >= '2007-04-01' and ts < '2007-04-02' group by ts::date; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=1993.93..1995.99 rows=137 width=8) InitPlan -> Index Scan using domains_domain on domains (cost=0.00..6.01 rows=1 width=8) Index Cond: ("domain" = '1234.com'::text) -> Result (cost=0.00..1986.69 rows=247 width=8) -> Append (cost=0.00..1986.07 rows=247 width=8) -> Seq Scan on views_ts (cost=0.00..1245.75 rows=1 width=8) Filter: ((domain_id = $0) AND (ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone)) -> Bitmap Heap Scan on views_ts_2007_04_01 views_ts (cost=2.86..740.32 rows=246 width=8) Recheck Cond: (domain_id = $0) Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone)) -> Bitmap Index Scan on views_ts_2007_04_01_domain_id (cost=0.00..2.86 rows=246 width=0) Index Cond: (domain_id = $0) --0-1215042455-1178172477=:5712 Content-Type: text/html; charset=ascii
Hi,

I am using postgres 8.1.3 for this.  If this has been dealt with later, please disregard.  And this is not a complaint or a request, I am just curious, so I know how to best construct my queries.

There is a unique index mapping domains to domain_ids.

views_ts specifies a partitioned table, where views_ts_2007_04_01 is the only partition matching the range given in the query.

My goal is to produce summaries of counts of rows for each day within a given range (can be days, months, years).

The issue: the second query results in a lower cost estimate.  I am wondering why the second query plan was not chosen for the first query.

Thanks!
Brian

live=> explain select ts::date,count(*) from views_ts join domains using (domain_id) where domain = '1234.com' and ts >= '2007-04-01' and ts < '2007-04-02' group by ts::date;
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=9040.97..9041.00 rows=2 width=8)
   ->  Hash Join  (cost=6.01..9040.96 rows=2 width=8)
         Hash Cond: ("outer".domain_id = "inner".domain_id)
         ->  Append  (cost=0.00..7738.01 rows=259383 width=16)
               ->  Seq Scan on views_ts  (cost=0.00..1138.50 rows=1 width=16)
                     Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
               ->  Seq Scan on views_ts_2007_04_01 views_ts  (cost=0.00..6599.51 rows=259382 width=16)
                     Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
         ->  Hash  (cost=6.01..6.01 rows=1 width=8)
               ->  Index Scan using domains_domain on domains  (cost=0.00..6.01 rows=1 width=8)
                     Index Cond: ("domain" = '1234.com'::text)
(11 rows)

live=> explain select ts::date,count(*) from views_ts where domain_id = (select domain_id from domains where domain = '1234.com') and ts >= '2007-04-01' and ts < '2007-04-02' group by ts::date;
                                                                                 QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1993.93..1995.99 rows=137 width=8)
   InitPlan
     ->  Index Scan using domains_domain on domains  (cost=0.00..6.01 rows=1 width=8)
           Index Cond: ("domain" = '1234.com'::text)
   ->  Result  (cost=0.00..1986.69 rows=247 width=8)
         ->  Append  (cost=0.00..1986.07 rows=247 width=8)
               ->  Seq Scan on views_ts  (cost=0.00..1245.75 rows=1 width=8)
                     Filter: ((domain_id = $0) AND (ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
               ->  Bitmap Heap Scan on views_ts_2007_04_01 views_ts  (cost=2.86..740.32 rows=246 width=8)
                     Recheck Cond: (domain_id = $0)
                     Filter: ((ts >= '2007-04-01 00:00:00+10'::timestamp with time zone) AND (ts < '2007-04-02 00:00:00+10'::timestamp with time zone))
                     ->  Bitmap Index Scan on views_ts_2007_04_01_domain_id  (cost=0.00..2.86 rows=246 width=0)
                           Index Cond: (domain_id = $0)

--0-1215042455-1178172477=:5712--