Re: partitioned table set and indexes

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 <andreas(at)a-kretschmer(dot)de
> wrote:

>
> > Rick Otten <rottenwindfish(at)gmail(dot)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
>