Weird, when I deleted an erroneous index it started picking a reasonable
plan. This now works as expected, for posterity here is the bad plan:

 Nested Loop  (cost=21281.50..21323812.82 rows=5621000 width=47) (actual
time=171.648..7233.298 rows=85615 loops=1)

   ->  Function Scan on generate_series dates  (cost=0.00..3.00 rows=1000
width=8) (actual time=0.031..0.252 rows=267 loops=1)

   ->  Unique  (cost=21281.50..21290.08 rows=5621 width=39) (actual
time=25.730..27.050 rows=321 loops=267)

         ->  Sort  (cost=21281.50..21284.36 rows=5724 width=39) (actual
time=25.728..26.242 rows=6713 loops=267)

               Sort Key: alloc.note_id, alloc.series_id

               Sort Method: quicksort  Memory: 2220kB

               ->  Nested Loop  (cost=10775.92..21210.05 rows=5724
width=39) (actual time=1.663..21.938 rows=6713 loops=267)

                     ->  Hash Join  (cost=10775.83..20355.61 rows=5724
width=52) (actual time=1.657..5.980 rows=6713 loops=267)

                           Hash Cond: (alloc.note_id = contrib.id)

                           ->  Bitmap Heap Scan on portfolio_allocations
alloc  (cost=69.82..9628.13 rows=5724 width=39) (actual time=1.010..2.278
rows=6713 loops=267)

                                 Recheck Cond: ((entity_id =
'\x5787f132f50f7b03002cf835'::bytea) AND (allocated_on <=
date(dates.dates)))

                                 Heap Blocks: exact=118074

                                 ->  Bitmap Index Scan on
portfolio_allocations_entity_id_allocated_on_idx  (cost=0.00..69.53
rows=5724 width=0) (actual time=0.956..0.956 rows=6713 lo

                                       Index Cond: ((entity_id =
'\x5787f132f50f7b03002cf835'::bytea) AND (allocated_on <=
date(dates.dates)))

                           ->  Hash  (cost=9464.85..9464.85 rows=354617
width=26) (actual time=169.792..169.792 rows=354617 loops=1)

                                 Buckets: 524288  Batches: 1  Memory Usage:
24296kB

                                 ->  Seq Scan on contributions contrib
 (cost=0.00..9464.85
rows=354617 width=26) (actual time=0.007..83.246 rows=354617 loops=1)

                     ->  Index Only Scan using investments_pkey on
investments inv  (cost=0.08..0.15 rows=1 width=13) (actual
time=0.002..0.002 rows=1 loops=1792457)

                           Index Cond: (id = contrib.investment_id)

                           Heap Fetches: 1792457

 Planning time: 0.721 ms

 Execution time: 7236.507 ms


On Tue, Dec 5, 2017 at 10:04 AM Alex Reece <awre...@gmail.com> wrote:

> I get very different plan chosen when my query is in a lateral subquery vs
> standalone -- it doesn't use a key when joining on a table, instead opting
> to do a hash join. Here is the query:
>
> select distinct on (sub.entity_id, sub.note_id, sub.series_id)
>        entity_id, note_id, series_id
> from
> (
> select alloc.entity_id, alloc.note_id, alloc.series_id, alloc.amount,
> inv.name
> from public.portfolio_allocations alloc
> JOIN contributions contrib on contrib.id = alloc.note_id
> JOIN investments inv on inv.id = contrib.investment_id
> where entity_id = '\x5787f132f50f7b03002cf835' and
> alloc.allocated_on <= dates.date
> ) sub
>
> And wrapped inside the lateral:
>
>         explain analyze
>         select *
>         from generate_series('2017-03-14 20:59:59.999'::TIMESTAMPTZ,
>       current_timestamp::TIMESTAMP + INTERVAL '1 day', '24 hours') dates,
>         LATERAL (
>          ... <SUB QUERY HERE> ...
>         ) lat
>
> Run by itself injecting a hard coded value for dates.date, I get the
> expected plan which uses a key index on contributions:
>
>       Unique  (cost=14.54..14.54 rows=2 width=39) (actual
> time=0.052..0.053 rows=2 loops=1)
>          ->  Sort  (cost=14.54..14.54 rows=2 width=39) (actual
> time=0.052..0.052 rows=2 loops=1)
>                Sort Key: alloc.note_id, alloc.series_id
>                Sort Method: quicksort  Memory: 25kB
>                ->  Nested Loop  (cost=0.25..14.53 rows=2 width=39) (actual
> time=0.030..0.042 rows=2      loops=1)
>                      ->  Nested Loop  (cost=0.17..14.23 rows=2 width=52)
> (actual time=0.022..0.028       rows=2 loops=1)
>                            ->  Index Scan using
> portfolio_allocations_entity_id_allocated_on_idx on
> portfolio_allocations alloc  (cost=0.09..6.05 rows=2 width=39) (actual
>  time=0.012..0.014
>                                  Index Cond: ((entity_id =
> '\x5787f132f50f7b03002cf835'::bytea) AND      (allocated_on <= '2017-03-14
> 20:59:59.999+00'::timestamp with time   zone))
>                            ->  Index Scan using
> contributions_id_accrue_from_idx on contributions     contrib
> (cost=0.08..4.09 rows=1 width=26) (actual time=0.005..0.005    rows=1
> loops=2)
>                                  Index Cond: (id = alloc.note_id)
>                      ->  Index Only Scan using investments_pkey on
> investments inv  (     cost=0.08..0.15 rows=1 width=13) (actual
> time=0.005..0.006 rows=1 loops=2)
>                            Index Cond: (id = contrib.investment_id)
>                            Heap Fetches: 2
>        Planning time: 0.617 ms
>        Execution time: 0.100 ms
>       (15 rows)
>
> But run in the lateral, it doesn't use the index:
>
>        Nested Loop  (cost=14.54..24.55 rows=2000 width=47) (actual
> time=0.085..0.219 rows=534      loops=1)
>          ->  Function Scan on generate_series dates  (cost=0.00..3.00
> rows=1000 width=8) (actual      time=0.031..0.043 rows=267 loops=1)
>          ->  Materialize  (cost=14.54..14.55 rows=2 width=39) (actual
> time=0.000..0.000 rows=2     loops=267)
>                ->  Unique  (cost=14.54..14.54 rows=2 width=39) (actual
> time=0.052..0.053 rows=2       loops=1)
>                      ->  Sort  (cost=14.54..14.54 rows=2 width=39) (actual
> time=0.051..0.052 rows=2      loops=1)
>                            Sort Key: alloc.note_id, alloc.series_id
>                            Sort Method: quicksort  Memory: 25kB
>                            ->  Nested Loop  (cost=0.25..14.53 rows=2
> width=39) (actual       time=0.029..0.041 rows=2 loops=1)
>                                  ->  Nested Loop  (cost=0.17..14.23 rows=2
> width=52) (actual       time=0.021..0.027 rows=2 loops=1)
>                                        ->  Index Scan using
>  portfolio_allocations_entity_id_allocated_on_idx on
> portfolio_allocations alloc  (cost=0.09..6.05 rows=2     width=39) (actual
> time=0
>                                              Index Cond: ((entity_id =
>  '\x5787f132f50f7b03002cf835'::bytea) AND (allocated_on      <= '2017-03-14
> 20:59:59.999+00'::timestamp with time    zone))
>                                        ->  Index Scan using
> contributions_id_accrue_from_idx on       contributions contrib
> (cost=0.08..4.09 rows=1 width=26) (     actual time=0.005..0.005 rows=1 loo
>                                              Index Cond: (id =
> alloc.note_id)
>                                  ->  Index Only Scan using
> investments_pkey on investments inv  (     cost=0.08..0.15 rows=1 width=13)
> (actual time=0.005..0.006 rows=1    loops=2)
>                                        Index Cond: (id =
> contrib.investment_id)
>                                        Heap Fetches: 2
>        Planning time: 0.718 ms
>        Execution time: 0.296 ms
>       (18 rows)
>
> For reference, here are the indexes on the relevant tables:
>
> Indexes:
>     "portfolio_allocations_entity_id_allocated_on_idx" btree (entity_id,
> allocated_on DESC)
>     "portfolio_allocations_note_id_allocated_on_idx" btree (note_id,
> allocated_on DESC)
>     "portfolio_allocations_pnsa" btree (entity_id, note_id, series_id,
> allocated_on DESC)
>
> Indexes:
>     "contributions_pkey" PRIMARY KEY, btree (id)
>     "contributions_id_accrue_from_idx" btree (id,
> events_earnings_accrue_from)
>
> I have a few questions here:
>   - Why doesn't it use the primary key index in either case?
>   - Why isn't it choosing portfolio_allocations_pnsa, which seems like it
> would prevent it from having to sort?
>
> Best,
> ~Alex
>

Reply via email to