Postgres Pro
Downloads
Home   >   mailing lists

Re: Simple join doesn't use index - Mailing list pgsql-performance

From Alex Vinnik
Subject Re: Simple join doesn't use index
Date
Msg-id CALd8TVFG00D-jLgMfX5eRau+hTpGuzsBm8O2n1kW4dtx-B49Ng@mail.gmail.com
Whole thread Raw
In response to Re: Simple join doesn't use index  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-performance
Tree view



On Tue, Jan 29, 2013 at 2:06 PM, Jeff Janes <jeff.janes@gmail.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

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

pgsql-performance by date:

Previous
From: Jeff Janes
Date:
Subject: Re: Simple join doesn't use index
Next
From: Alex Vinnik
Date:
Subject: Re: Simple join doesn't use index