Why does it index scan when I use where, but not when I do a join? On Fri, Dec 11, 2015 at 7:20 PM, Andreas Kretschmer <andr...@a-kretschmer.de > wrote:
> > > Rick Otten <rottenwindf...@gmail.com> hat am 11. Dezember 2015 um 23:09 > > geschrieben: > > > > > The query performance hit for sequence scanning isn't all that terrible, > > but I'd rather understand and get rid of the issue if I can, now, before > I > > run into it again in a situation where it is crippling. > > i think, you should try to understand how the planner works. > > a simple example: > > test=# create table foo (id serial primary key, val text); > CREATE TABLE > test=*# insert into foo (val) select repeat(md5(1::text), 5); > INSERT 0 1 > test=*# analyse foo; > ANALYZE > test=*# explain analyse select val from foo where id=1; > QUERY PLAN > > ----------------------------------------------------------------------------------------------- > Seq Scan on foo (cost=0.00..1.02 rows=1 width=164) (actual > time=0.006..0.007 > rows=1 loops=1) > Filter: (id = 1) > Rows Removed by Filter: 1 > Planning time: 0.118 ms > Execution time: 0.021 ms > (5 rows) > > > As you can see a seq-scan. It's a small table, costs ..1.02. > > Adding one row: > > test=*# insert into foo (val) select val from foo; > INSERT 0 1 > test=*# analyse foo; > ANALYZE > test=*# explain analyse select val from foo where id=1; > QUERY PLAN > > ----------------------------------------------------------------------------------------------- > Seq Scan on foo (cost=0.00..1.02 rows=1 width=164) (actual > time=0.006..0.007 > rows=1 loops=1) > Filter: (id = 1) > Rows Removed by Filter: 1 > Planning time: 0.118 ms > Execution time: 0.021 ms > (5 rows) > > > The same plan. Adding 2 rows: > > test=*# insert into foo (val) select val from foo; > INSERT 0 2 > test=*# analyse foo; > ANALYZE > test=*# explain analyse select val from foo where id=1; > QUERY PLAN > > ----------------------------------------------------------------------------------------------- > Seq Scan on foo (cost=0.00..1.05 rows=1 width=164) (actual > time=0.220..0.277 > rows=1 loops=1) > Filter: (id = 1) > Rows Removed by Filter: 3 > Planning time: 0.149 ms > Execution time: 0.453 ms > (5 rows) > > > The same plan. Adding more rows: > > test=*# insert into foo (val) select val from foo; > INSERT 0 4 > test=*# insert into foo (val) select val from foo; > INSERT 0 8 > test=*# insert into foo (val) select val from foo; > INSERT 0 16 > test=*# insert into foo (val) select val from foo; > INSERT 0 32 > test=*# insert into foo (val) select val from foo; > INSERT 0 64 > test=*# insert into foo (val) select val from foo; > INSERT 0 128 > test=*# insert into foo (val) select val from foo; > INSERT 0 256 > test=*# insert into foo (val) select val from foo; > INSERT 0 512 > test=*# insert into foo (val) select val from foo; > INSERT 0 1024 > test=*# insert into foo (val) select val from foo; > INSERT 0 2048 > test=*# insert into foo (val) select val from foo; > INSERT 0 4096 > test=*# analyse foo; > ANALYZE > test=*# explain analyse select val from foo where id=1; > QUERY PLAN > > ---------------------------------------------------------------------------------------------------------------- > Index Scan using foo_pkey on foo (cost=0.28..8.30 rows=1 width=164) > (actual > time=0.007..0.008 rows=1 loops=1) > Index Cond: (id = 1) > Planning time: 0.120 ms > Execution time: 0.024 ms > (4 rows) > > > We got a new plan! Index-Scan now. We are looking now in pg_class to see > how > many rows and pages we have: > > test=*# select relpages, reltuples from pg_class where relname = 'foo'; > relpages | reltuples > ----------+----------- > 200 | 8192 > (1 row) > > How large ist the Index? > > test=*# select relpages, reltuples from pg_class where relname = > 'foo_pkey'; > relpages | reltuples > ----------+----------- > 25 | 8192 > (1 row) > > > > So, now it's cheaper to read the index and than do an index-scan on the > heap to > read one record (our where-condition is on the primary key, so only one row > expected, one page have to read with random access) > > > > It's simple math! If you want to learn more you can find a lot about that > via > google: > > https://www.google.de/?gws_rd=ssl#q=explaining+explain >
