Re: Need help optimizing this query - Mailing list pgsql-general
From | Pat Maddox |
---|---|
Subject | Re: Need help optimizing this query |
Date | |
Msg-id | 810a540e0707181328m46ce0497mef43b064e8b5e1f4@mail.gmail.com Whole thread Raw |
In response to | Need help optimizing this query ("Pat Maddox" <pergesu@gmail.com>) |
Responses |
Re: Need help optimizing this query
|
List | pgsql-general |
On 7/18/07, Pat Maddox <pergesu@gmail.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)
pgsql-general by date: