On Sun, May 31, 2015, at 12:53 AM, Tom Lane wrote: > "Glen M. Witherington" <glen@xxxxxx> 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.019 rows=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 Wow, sorry I screwed up the query. It should be: ORDER BY c.created_at DESC Not b, or as you noted its trivial to index. Sorry! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
