Return to Answer

added 1056 characters in body

Source Link

edited Apr 20, 2017 at 23:50

13.6k
8
39
66

The reason why PostgreSQL decides to perform a Sequential Scan of the listings table is because there is just one row. In that case, reading the whole table takes less time than using an index.

If you just add more data to the listings table, PostgreSQL changes plans, because then, reading the whole table is no longer the cheapest alternative:

dbfiddle here

PostgreSQL (and most other databases) has a Query Planner that chooses an execution plan based on a cost-estimate of all available alternatives. The cost-estimate is based on the amount and statistical distribution of data within the different columns of the different tables, and takes into consideration the availability of indexes, but will choose to use them only if it is the cheapest alternative (or you force it to use them via settings).

With a query as simple as thisthe one in this example, just joining two tables using an equality JOIN condition, you shouldn't need to outsmart PostgreSQL query planner. Let the planner plan. It will nearly always choose the best plan.

You may need to play some tricks when JOINs are much more complicated (many: many more tables, conditions which aren't necessarily just equalities, function calls, correlations between tables that the planner doesn't take into account, etc. ... and the planner ends up making bad estimates oncost-estimates, and the number of rows to be retrieved in every case)estimated cheapest plan is not really the cheapest.

If you just add more data to the listings table, PostgreSQL changes plans:

dbfiddle here

With a query as simple as this one, just joining two tables using an equality JOIN condition, you shouldn't need to outsmart PostgreSQL query planner. Let the planner plan. You may need to play some tricks when JOINs are much more complicated (many more tables, conditions which aren't necessarily just equalities, function calls... and the planner making bad estimates on the number of rows to be retrieved in every case).

The reason why PostgreSQL decides to perform a Sequential Scan of the listings table is because there is just one row. In that case, reading the whole table takes less time than using an index.

If you just add more data to the listings table, PostgreSQL changes plans, because then, reading the whole table is no longer the cheapest alternative:

dbfiddle here

With a query as simple as the one in this example, just joining two tables using an equality JOIN condition, you shouldn't need to outsmart PostgreSQL query planner. Let the planner plan. It will nearly always choose the best plan.

You may need to play some tricks when JOINs are much more complicated: many more tables, conditions which aren't necessarily just equalities, function calls, correlations between tables that the planner doesn't take into account, etc. ... and the planner ends up making bad cost-estimates, and the estimated cheapest plan is not really the cheapest.

Source Link

created Apr 20, 2017 at 23:39

joanolo

13.6k
8
39
66

If you look carefully at your query plan, you will find that the Seq Scan expects to find just one row (rows=1 width=840) in the listings table.

This is what happens if you have a situation such as this:

 CREATE TABLE users 
 (
   id text PRIMARY KEY,
   internal_id serial,
   something_else char(800)
 ) ;
 
 CREATE TABLE listings 
 (
   id bigserial PRIMARY KEY,
   created_at timestamptz default current_timestamp,
   posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE
 ) ;

We add a few users, but just ONE listing:

 INSERT INTO users (id) VALUES('1009280753550922') ;
 INSERT INTO users (id) SELECT generate_series(1, 1000)::text ;
 
 INSERT INTO listings (posted_by) VALUES('1009280753550922') ;

Have the tables analyzed, so that the query planner has got good statistics:

 ANALYZE users;
 ANALYZE listings;

And make it explain the SELECT:

 EXPLAIN
 SELECT 
     *, users.internal_id 
 FROM 
     listings 
     JOIN users ON listings.posted_by = users.id 
 WHERE 
     listings.id = 2 
     and posted_by = '1009280753550922';

 | QUERY PLAN                                                                      |
 | :------------------------------------------------------------------------------ |
 | Nested Loop  (cost=0.28..9.32 rows=1 width=3248)                                |
 |   ->  Seq Scan on listings  (cost=0.00..1.01 rows=1 width=33)                   |
 |         Filter: ((posted_by = '1009280753550922'::text) AND (id = 2))           |
 |   ->  Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=3211) |
 |         Index Cond: (id = '1009280753550922'::text)                             |

dbfiddle here

If you just add more data to the listings table, PostgreSQL changes plans:

 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;
 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;
 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;

 EXPLAIN
 SELECT 
     *, users.internal_id 
 FROM 
     listings 
     JOIN users ON listings.posted_by = users.id 
 WHERE 
     listings.id = 2 
     and posted_by = '1009280753550922';

 | QUERY PLAN                                                                          |
 | :---------------------------------------------------------------------------------- |
 | Nested Loop  (cost=0.56..16.60 rows=1 width=3234)                                   |
 |   ->  Index Scan using listings_pkey on listings  (cost=0.28..8.30 rows=1 width=19) |
 |         Index Cond: (id = 2)                                                        |
 |         Filter: (posted_by = '1009280753550922'::text)                              |
 |   ->  Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=3211)     |
 |         Index Cond: (id = '1009280753550922'::text)                                 |

dbfiddle here