Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 4B8499FBE61
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Jun 2007 15:35:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
	with ESMTP id 90687-08-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Jun 2007 15:35:20 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from glacier.frostconsultingllc.com (glacier.frostconsultingllc.com
	[69.36.227.170])
	by postgresql.org (Postfix) with ESMTP id 425059FBD32
	for <pgsql-sql@postgresql.org>; Tue, 19 Jun 2007 15:34:04 -0300 (ADT)
Received: from localhost ([127.0.0.1]:32981)
	by glacier.frostconsultingllc.com with esmtp (Exim 4.67)
	(envelope-from <jeff@frostconsultingllc.com>)
	id 1I0iWh-0005dL-6o; Tue, 19 Jun 2007 11:34:03 -0700
Date: Tue, 19 Jun 2007 11:34:03 -0700 (PDT)
From: Jeff Frost <jeff@frostconsultingllc.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: pgsql-sql@postgresql.org
Subject: Re: separating improperly grouped page views 
In-Reply-To: 
 <Pine.LNX.4.64.0706172306510.14506@discord.home.frostconsultingllc.com>
Message-ID: <Pine.LNX.4.64.0706191018050.24164@glacier.frostconsultingllc.com>
References: 
 <Pine.LNX.4.64.0706172157410.14506@discord.home.frostconsultingllc.com>
	<2169.1182146093@sss.pgh.pa.us>
	<Pine.LNX.4.64.0706172306510.14506@discord.home.frostconsultingllc.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
X-SA-Exim-Connect-IP: 127.0.0.1
X-SA-Exim-Mail-From: jeff@frostconsultingllc.com
X-SA-Exim-Scanned: No (on glacier.frostconsultingllc.com);
	SAEximRunCond expanded to false
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200706/146
X-Sequence-Number: 28636

On Sun, 17 Jun 2007, Jeff Frost wrote:

> -------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on page_view pv1  (cost=0.00..11529031.34 rows=3580205 width=239)
>   SubPlan
>     ->  Result  (cost=1.58..1.59 rows=1 width=0)
>           InitPlan
>             ->  Limit  (cost=0.00..1.58 rows=1 width=8)
>                   ->  Index Scan Backward using page_view_visit_id_stamp_idx 
> on page_view pv2  (cost=0.00..625.39 rows=397 width=8)
>                         Index Cond: ((visit_id = $0) AND (stamp < $1))
>                         Filter: (stamp IS NOT NULL)
>     ->  Result  (cost=1.58..1.59 rows=1 width=0)
>           InitPlan
>             ->  Limit  (cost=0.00..1.58 rows=1 width=8)
>                   ->  Index Scan Backward using page_view_visit_id_stamp_idx 
> on page_view pv2  (cost=0.00..625.39 rows=397 width=8)
>                         Index Cond: ((visit_id = $0) AND (stamp < $1))
>                         Filter: (stamp IS NOT NULL)
> (14 rows)
>
> Compared to:
>
>
> -------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on page_view pv1  (cost=0.00..2622541458.55 rows=3596473 width=237)
>   SubPlan
>     ->  Result  (cost=364.56..364.57 rows=1 width=0)
>           InitPlan
>             ->  Limit  (cost=0.00..364.56 rows=1 width=8)
>                   ->  Index Scan Backward using page_view_stamp_idx on 
> page_view pv2  (cost=0.00..153481.58 rows=421 width=8)
>                         Index Cond: (stamp < $1)
>                         Filter: ((stamp IS NOT NULL) AND (visit_id = $0))
>     ->  Result  (cost=364.56..364.57 rows=1 width=0)
>           InitPlan
>             ->  Limit  (cost=0.00..364.56 rows=1 width=8)
>                   ->  Index Scan Backward using page_view_stamp_idx on 
> page_view pv2  (cost=0.00..153481.58 rows=421 width=8)
>                         Index Cond: (stamp < $1)
>                         Filter: ((stamp IS NOT NULL) AND (visit_id = $0))
> (14 rows)

And throwing the ORDER BY back in reduces the cost even more!

                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
  Subquery Scan x  (cost=0.00..5815824.15 rows=3629753 width=1186)
    ->  Index Scan using page_view_visit_idx on page_view pv1  (cost=0.00..5743229.09 rows=3629753 width=237)
          SubPlan
            ->  Result  (cost=1.51..1.52 rows=1 width=0)
                  InitPlan
                    ->  Limit  (cost=0.00..1.51 rows=1 width=8)
                          ->  Index Scan Backward using page_view_visit_id_stamp_idx on page_view pv2  (cost=0.00..608.41 rows=402 width=8)
                                Index Cond: ((visit_id = $0) AND (stamp < $1))
                                Filter: (stamp IS NOT NULL)
(9 rows)

Now we only have to do that index scan once. :-)  I had foolishly taken that 
out to see if the sort was killing me and forgot to put it back in.

So now it's:

  Subquery Scan x  (cost=0.00..5815824.15 rows=3629753 width=1186)
  vs
Seq Scan on page_view pv1  (cost=0.00..11529031.34 rows=3580205 width=239)
  vs
Seq Scan on page_view pv1  (cost=0.00..2622541458.55 rows=3596473 width=237)

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954