Re: Efficient sorting the results of a join, without denormalization - Mailing list pgsql-general

From Tom Lane
Subject Re: Efficient sorting the results of a join, without denormalization
Date
Msg-id 18496.1433051617@sss.pgh.pa.us
Whole thread Raw
In response to Efficient sorting the results of a join, without denormalization  ("Glen M. Witherington" <glen@fea.st>)
Responses Re: Efficient sorting the results of a join, without denormalization
List pgsql-general
"Glen M. Witherington" <glen@fea.st> writes:
> And here's the query I want to do, efficiently:

> SELECT * FROM c
>   JOIN b ON b.id = c.b_id
>   JOIN a ON a.id = b.a_id
> WHERE a.id = 3
> ORDER BY b.created_at DESC
> LIMIT 10

At least for that dummy data, this seems sufficient:

regression=# create index on b (a_id, created_at);
CREATE INDEX
regression=# explain analyze SELECT * FROM c
  JOIN b ON b.id = c.b_id
  JOIN a ON a.id = b.a_id
WHERE a.id = 3
ORDER BY b.created_at DESC
LIMIT 10;
                                                                      QUERY PLAN
                              

------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.14..21.95 rows=10 width=64) (actual time=0.064..1.176 rows=10 loops=1)
   ->  Nested Loop  (cost=0.14..436079.81 rows=200000 width=64) (actual time=0.063..1.173 rows=10 loops=1)
         Join Filter: (b.id = c.b_id)
         Rows Removed by Join Filter: 1218
         ->  Nested Loop  (cost=0.14..9.81 rows=20 width=40) (actual time=0.035..0.035 rows=1 loops=1)
               ->  Index Scan Backward using b_a_id_created_at_idx on b  (cost=0.14..8.49 rows=20 width=24) (actual
time=0.019..0.019rows=1 loops=1) 
                     Index Cond: (a_id = 3)
               ->  Materialize  (cost=0.00..1.07 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)
                     ->  Seq Scan on a  (cost=0.00..1.06 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)
                           Filter: (id = 3)
                           Rows Removed by Filter: 2
         ->  Materialize  (cost=0.00..27230.00 rows=1000000 width=24) (actual time=0.008..0.811 rows=1228 loops=1)
               ->  Seq Scan on c  (cost=0.00..16370.00 rows=1000000 width=24) (actual time=0.007..0.310 rows=1228
loops=1)
 Planning time: 0.796 ms
 Execution time: 1.390 ms
(15 rows)

            regards, tom lane


pgsql-general by date:

Previous
From: Rishi Gokhale
Date:
Subject: date type changing to timestamp without time zone in postgres 9.4
Next
From: Andreas Kretschmer
Date:
Subject: Re: replacing jsonb field value