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
Date: Tue, 19 Jun 2007 11:34:03 -0700 (PDT)
Message-ID: <Pine.LNX.4.64.0706191018050.24164@glacier.frostconsultingllc.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0706172306510.14506@discord.home.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff@frostconsultingllc.com, tgl@sss.pgh.pa.us
Subject: Re: separating improperly grouped page views
In-Reply-To: <Pine.LNX.4.64.0706191018050.24164@glacier.frostconsultingllc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox