Re: Simple join doesn't use index

On Tue, Jan 29, 2013 at 2:06 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> > Sort Key: visits.id, views.id
> > Sort Method: external sort Disk: 4248kB
>
> What query are you running? The query you originally showed us should
> not be doing this sort in the first place.
>
> Cheers,
>
> Jeff
>

Here is the query

select visits.id, views.id
from visits join views on visits.id = views.visit_id
where visits.created_at >= '01/15/2013' and visits.created_at < '01/16/2013'
order by visits.id, views.id;

Original query didn't have order by clause

Here query plan w/o order by
Merge Join (cost=18213.46..802113.80 rows=182579 width=8) (actual
time=144443.693..145469.499 rows=209401 loops=1)
Merge Cond: (visits.id = views.visit_id)
-> Sort (cost=18195.47..18523.91 rows=131373 width=4) (actual
time=335.496..464.929 rows=131311 loops=1)
Sort Key: visits.id
Sort Method: quicksort Memory: 12300kB
-> Index Scan using visits_created_at_index on visits
(cost=0.00..7026.59 rows=131373 width=4) (actual time=0.037..162.047
rows=131311 loops=1)
Index Cond: ((created_at >= '2013-01-15 00:00:00'::timestamp
without time zone) AND (created_at < '2013-01-16 00:00:00'::timestamp
without time zone))
-> Index Scan using views_visit_id_visit_buoy_index on views
(cost=0.00..766120.99 rows=6126002 width=8) (actual
time=18.960..140565.130 rows=4014837 loops=1)
Total runtime: 145664.274 ms