Re: efficient storing of urls

On Mon, Mar 01, 2004 at 08:54:43AM -0700, scott.marlowe wrote:
> On Fri, 27 Feb 2004, Shane Wegner wrote:
> > A select to display the 100 most popular pages:
> > explain analyze select count(*) as c,url from hits,urls where hit_date between '2004-01-01' and '2004-01-31' and url_id=urls.id group by url order by c desc limit 100;
> > QUERY PLAN
> > ------------------------------------------------------------------------------------------------------------------------------------------------------
> > Limit (cost=320189.71..320189.96 rows=100 width=68) (actual time=34156.080..34156.324 rows=100 loops=1)
> > -> Sort (cost=320189.71..320700.06 rows=204138 width=68) (actual time=34156.068..34156.208 rows=100 loops=1)
> > Sort Key: count(*)
> > -> GroupAggregate (cost=281214.19..283255.57 rows=204138 width=68) (actual time=32457.857..33584.861 rows=53460 loops=1)
> > -> Sort (cost=281214.19..281724.54 rows=204138 width=68) (actual time=32457.690..32873.446 rows=248888 loops=1)
> > Sort Key: urls.url
> > -> Merge Join (cost=239594.05..244280.05 rows=204138 width=68) (actual time=21363.547..24385.213 rows=248888 loops=1)
> > Merge Cond: ("outer".url_id = "inner".id)
> > -> Sort (cost=168400.38..168914.15 rows=205508 width=4) (actual time=14785.934..15156.772 rows=249350 loops=1)
> > Sort Key: hits.url_id
> > -> Seq Scan on hits (cost=0.00..148512.07 rows=205508 width=4) (actual time=40.265..12081.506 rows=249350 loops=1)
> > Filter: ((hit_date >= '2004-01-01'::date) AND (hit_date <= '2004-01-31'::date))
> > -> Sort (cost=71193.67..72005.68 rows=324805 width=72) (actual time=6577.430..7422.945 rows=519307 loops=1)
> > Sort Key: urls.id
> > -> Seq Scan on urls (cost=0.00..7389.05 rows=324805 width=72) (actual time=0.110..1187.617 rows=324805 loops=1)
> > Total runtime: 34221.250 ms
> > (16 rows)
>
> Your single biggest jump in actual time seems to be coming from the seq
> scan on hits, which takes you from 40 ms to 12,000 ms.
>
> Can you index the hit_date and see if that helps?

Hmm, I don't think it's using the index. I tried two indexes
create index hit_date on hits(hit_date);
create index hit_date2 on hits(hit_date,url_id);
vacuum analyze;

With the hope that it would use the multicolumn index for
hit_date and url_id but it's still doing a seq scan on
hits.

explain analyze select count(*) as c,url from hits,urls where hit_date between
'2004-01-01' and '2004-01-31' and url_id=urls.id group by url order by c desc
limit 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=300458.08..300458.33 rows=100 width=68) (actual time=21117.590..21117.842 rows=100 loops=1)
-> Sort (cost=300458.08..301021.65 rows=225429 width=68) (actual time=21117.579..21117.718 rows=100 loops=1)
Sort Key: count(*)
-> GroupAggregate (cost=256840.55..259094.84 rows=225429 width=68) (actual time=19645.926..20671.608 rows=53460 loops=1)
-> Sort (cost=256840.55..257404.12 rows=225429 width=68) (actual time=19645.841..20000.521 rows=248888 loops=1)
Sort Key: urls.url
-> Merge Join (cost=163951.08..215477.30 rows=225429 width=68) (actual time=10623.308..13332.906 rows=248888 loops=1)
Merge Cond: ("outer".id = "inner".url_id)
-> Index Scan using urls_pkey on urls (cost=0.00..47329.44 rows=326181 width=72) (actual time=0.109..1063.677 rows=326181 loops=1)
-> Sort (cost=163951.08..164519.00 rows=227170 width=4) (actual time=10622.954..10966.062 rows=248889 loops=1)
Sort Key: hits.url_id
-> Seq Scan on hits (cost=0.00..141717.33 rows=227170 width=4) (actual time=0.282..8229.634 rows=249350 loops=1)
Filter: ((hit_date >= '2004-01-01'::date) AND (hit_date <= '2004-01-31'::date))
Total runtime: 21160.919 ms
(14 rows)

S