From: | "Pat Maddox" <pergesu(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need help optimizing this query |
Date: | 2007-07-18 20:28:59 |
Message-ID: | 810a540e0707181328m46ce0497mef43b064e8b5e1f4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7/18/07, Pat Maddox <pergesu(at)gmail(dot)com> wrote:
> I've got a query that's taking forever (as will be obvious when you
> see it and the explain output). I can't figure out what indexes to
> add to make this run faster. I'd appreciate any help.
>
> Pat
>
>
>
> SELECT
> SUM(CASE WHEN (hit IS TRUE AND logged_in IS TRUE) THEN 1 ELSE 0 END)
> AS count_hits_console,
> SUM(CASE WHEN (hit IS TRUE AND logged_in IS FALSE) THEN 1 ELSE 0 END)
> AS count_hits_remote,
> SUM(CASE WHEN (played IS TRUE AND logged_in IS TRUE) THEN 1 ELSE 0
> END) AS count_played_console,
> SUM(CASE WHEN (played IS TRUE AND logged_in IS FALSE) THEN 1 ELSE 0
> END) AS count_played_remote,
> SUM(CASE WHEN (downloaded IS TRUE AND logged_in IS TRUE) THEN 1 ELSE
> 0 END) AS count_downloaded_console,
> SUM(CASE WHEN (downloaded IS TRUE AND logged_in IS FALSE) THEN 1 ELSE
> 0 END) AS count_downloaded_remote,
> SUM(CASE WHEN ((played IS TRUE OR downloaded IS TRUE) AND logged_in
> IS TRUE) THEN assets.size ELSE 0 END) as download_size_console,
> SUM(CASE WHEN ((played IS TRUE OR downloaded IS TRUE) AND logged_in
> IS FALSE) THEN assets.size ELSE 0 END) as download_size_remote,
> videos.id, videos.title, videos.guid FROM video_views, assets, videos
> WHERE videos.company_id=1 AND video_views.video_id=videos.id AND
> video_views.asset_id=assets.id GROUP BY videos.id, videos.title,
> videos.guid ORDER BY count_hits_remote DESC LIMIT 100
>
>
>
>
> Limit (cost=139735.51..139735.68 rows=69 width=64)
> -> Sort (cost=139735.51..139735.68 rows=69 width=64)
> Sort Key: sum(CASE WHEN ((video_views.hit IS TRUE) AND
> (video_views.logged_in IS FALSE)) THEN 1 ELSE 0 END)
> -> HashAggregate (cost=139731.33..139733.40 rows=69 width=64)
> -> Hash Join (cost=1164.79..138880.04 rows=30956 width=64)
> Hash Cond: (video_views.asset_id = assets.id)
> -> Hash Join (cost=324.39..137343.13 rows=30956 width=60)
> Hash Cond: (video_views.video_id = videos.id)
> -> Seq Scan on video_views
> (cost=0.00..114500.13 rows=5922413 width=12)
> -> Hash (cost=323.52..323.52 rows=69 width=52)
> -> Bitmap Heap Scan on videos
> (cost=64.90..323.52 rows=69 width=52)
> Recheck Cond: (company_id = 1)
> -> Bitmap Index Scan on
> complete_videos_without_deleted_at (cost=0.00..64.88 rows=69 width=0)
> Index Cond: (company_id = 1)
> -> Hash (cost=645.18..645.18 rows=15618 width=12)
> -> Seq Scan on assets (cost=0.00..645.18
> rows=15618 width=12)
>
Here are the indexes I already have on the table:
"video_views_pkey" PRIMARY KEY, btree (id)
"index_video_views_on_asset_id" btree (asset_id)
"index_video_views_on_video_id" btree (video_id)
"index_video_views_on_video_id_and_asset_id_and_created_at" btree
(video_id, created_at, asset_id)
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Reyes | 2007-07-18 20:46:21 | Feature request: Per database search_path |
Previous Message | Tatsuo Ishii | 2007-07-18 20:24:33 | Re: Sylph-Searcher 1.0.0 released |