Seqscan on big table, when an Index-Usage should be possible

Hello,

I'm currently testing some queries on data which I had imported from an
other database-system into Postgres 9.4.

After the import I did create the indexes, run an analyze and vacuum. I
also played a little bit with seq_page_cost and random_page_cost. But
currently I have no clue, which parameter I have to adjust, to get an
query-time like the example width 'enable_seqscan=off'.

Stefan



> pd=> set enable_seqscan=off;
> pd=> explain analyze select t.name from product p left join measurements m on p.productid=m.productid inner join
measurementstypet on m.measurementstypeid=t.measurementstypeid where p.timestamp between '2013-02-01 15:00:00' and
'2013-02-0521:30:00' group by t.name; 
>                                                                                      QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> HashAggregate  (cost=200380892.01..200380936.43 rows=4442 width=16) (actual time=34428.335..34428.693 rows=656
loops=1)
>    Group Key: t.name
>    ->  Hash Join  (cost=8995.44..200361772.19 rows=7647926 width=16) (actual time=103.670..30153.958 rows=5404751
loops=1)
>          Hash Cond: (m.measurementstypeid = t.measurementstypeid)
>          ->  Nested Loop  (cost=8279.61..200188978.03 rows=7647926 width=4) (actual time=75.939..22488.725
rows=5404751loops=1) 
>                ->  Bitmap Heap Scan on product p  (cost=8279.03..662659.76 rows=526094 width=8) (actual
time=75.903..326.850rows=368494 loops=1) 
>                      Recheck Cond: (("timestamp" >= '2013-02-01 15:00:00'::timestamp without time zone) AND
("timestamp"<= '2013-02-05 21:30:00'::timestamp without time zo 
>                      Heap Blocks: exact=3192
>                      ->  Bitmap Index Scan on product_timestamp  (cost=0.00..8147.51 rows=526094 width=0) (actual
time=75.050..75.050rows=368494 loops=1) 
>                            Index Cond: (("timestamp" >= '2013-02-01 15:00:00'::timestamp without time zone) AND
("timestamp"<= '2013-02-05 21:30:00'::timestamp without tim 
>                ->  Index Scan using measurements_productid on measurements m  (cost=0.58..347.12 rows=3214 width=12)
(actualtime=0.018..0.045 rows=15 loops=368494) 
>                      Index Cond: (productid = p.productid)
>          ->  Hash  (cost=508.91..508.91 rows=16554 width=20) (actual time=27.704..27.704 rows=16554 loops=1)
>                Buckets: 2048  Batches: 1  Memory Usage: 686kB
>                ->  Index Scan using measurementstype_pkey on measurementstype t  (cost=0.29..508.91 rows=16554
width=20)(actual time=0.017..15.719 rows=16554 loops=1) 
> Planning time: 2.176 ms
> Execution time: 34429.080 ms
> (17 Zeilen)
>
>
> Zeit: 34432,187 ms
> pd=> set enable_seqscan=on;
> SET
> Zeit: 0,193 ms
> pd=> explain analyze select t.name from product p left join measurements m on p.productid=m.productid inner join
measurementstypet on m.measurementstypeid=t.measurementstypeid where p.timestamp between '2013-02-01 15:00:00' and
'2013-02-0521:30:00' group by t.name; 
>                                                                                         QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> HashAggregate  (cost=108645282.49..108645326.91 rows=4442 width=16) (actual time=5145182.269..5145182.656 rows=656
loops=1)
>    Group Key: t.name
>    ->  Hash Join  (cost=671835.40..108626162.68 rows=7647926 width=16) (actual time=2087822.232..5141351.539
rows=5404751loops=1) 
>          Hash Cond: (m.measurementstypeid = t.measurementstypeid)
>          ->  Hash Join  (cost=671291.94..108453540.88 rows=7647926 width=4) (actual time=2087800.816..5134312.822
rows=5404751loops=1) 
>                Hash Cond: (m.productid = p.productid)
>                ->  Seq Scan on measurements m  (cost=0.00..49325940.08 rows=2742148608 width=12) (actual
time=0.007..2704591.045rows=2742146806 loops=1) 
>                ->  Hash  (cost=662659.76..662659.76 rows=526094 width=8) (actual time=552.480..552.480 rows=368494
loops=1)
>                      Buckets: 16384  Batches: 4  Memory Usage: 2528kB
>                      ->  Bitmap Heap Scan on product p  (cost=8279.03..662659.76 rows=526094 width=8) (actual
time=73.353..302.482rows=368494 loops=1) 
>                            Recheck Cond: (("timestamp" >= '2013-02-01 15:00:00'::timestamp without time zone) AND
("timestamp"<= '2013-02-05 21:30:00'::timestamp without t 
>                            Heap Blocks: exact=3192
>                            ->  Bitmap Index Scan on product_timestamp  (cost=0.00..8147.51 rows=526094 width=0)
(actualtime=72.490..72.490 rows=368494 loops=1) 
>                                  Index Cond: (("timestamp" >= '2013-02-01 15:00:00'::timestamp without time zone) AND
("timestamp"<= '2013-02-05 21:30:00'::timestamp witho 
>          ->  Hash  (cost=336.54..336.54 rows=16554 width=20) (actual time=21.377..21.377 rows=16554 loops=1)
>                Buckets: 2048  Batches: 1  Memory Usage: 686kB
>                ->  Seq Scan on measurementstype t  (cost=0.00..336.54 rows=16554 width=20) (actual time=0.008..9.849
rows=16554loops=1) 
> Planning time: 2.236 ms
> Execution time: 5145183.471 ms
> (19 Zeilen)
>
> Zeit: 5145186,786 ms


> pd=> \d measurements
>                                                 Tabelle „public.measurements“
>        Spalte       |             Typ             |                                 Attribute
>
--------------------+-----------------------------+---------------------------------------------------------------------------
>  measurementsid     | bigint                      | not null Vorgabewert
nextval('measurements_measurementsid_seq'::regclass)
>  value              | text                        | not null
>  lowerlimit         | text                        |
>  upperlimit         | text                        |
>  measurementstypeid | integer                     | not null
>  productid          | bigint                      | not null
>  timestamp          | timestamp without time zone |
>  state              | character varying(20)       | not null Vorgabewert 'Unknown'::character varying
> Indexe:
>     "measurements_pkey" PRIMARY KEY, btree (measurementsid)
>     "measurements_measurementstypeid" btree (measurementstypeid)
>     "measurements_productid" btree (productid)
>
> pd=> \d product
>                                              Tabelle „public.product“
>       Spalte      |             Typ             |                            Attribute
> ------------------+-----------------------------+-----------------------------------------------------------------
>  productid        | bigint                      | not null Vorgabewert nextval('product_productid_seq'::regclass)
>  ordermaterialsid | integer                     | not null
>  testerid         | integer                     |
>  equipmentid      | integer                     | not null
>  timestamp        | timestamp without time zone | not null
>  state            | character varying(20)       | not null Vorgabewert 'Unknown'::character varying
>  exported         | character varying(1)        | not null Vorgabewert 'N'::character varying
>  mc_selectionid   | integer                     |
> Indexe:
>     "product_pkey" PRIMARY KEY, btree (productid)
>     "product_equipmentid" btree (equipmentid)
>     "product_exported" btree (exported)
>     "product_mc_selectionid" btree (mc_selectionid)
>     "product_ordermaterialsid" btree (ordermaterialsid)
>     "product_state" btree (state)
>     "product_testerid" btree (testerid)
>     "product_timestamp" btree ("timestamp")
> Fremdschlüssel-Constraints:
>     "fk_equipmentid" FOREIGN KEY (equipmentid) REFERENCES equipment(equipmentid) ON UPDATE CASCADE ON DELETE RESTRICT
>     "fk_mc_selectionid" FOREIGN KEY (mc_selectionid) REFERENCES mc_selection(mc_selectionid) ON UPDATE CASCADE ON
DELETESET NULL 
>     "fk_ordermaterialsid" FOREIGN KEY (ordermaterialsid) REFERENCES ordermaterials(ordermaterialsid) ON UPDATE
CASCADEON DELETE RESTRICT 
>     "fk_testerid" FOREIGN KEY (testerid) REFERENCES tester(testerid) ON UPDATE CASCADE ON DELETE RESTRICT
>
> pd=> \d measurementstype
>                                                 Tabelle „public.measurementstype“
>        Spalte       |          Typ           |                                     Attribute
>
--------------------+------------------------+-----------------------------------------------------------------------------------
>  measurementstypeid | integer                | not null Vorgabewert
nextval('measurementstype_measurementstypeid_seq'::regclass)
>  datatype           | character varying(20)  | not null Vorgabewert 'char'::character varying
>  name               | character varying(255) | not null
>  description        | character varying(255) | Vorgabewert NULL::character varying
>  unit               | character varying(20)  | Vorgabewert NULL::character varying
>  step               | integer                |
>  stepdescription    | character varying(255) | Vorgabewert NULL::character varying
>  permissionlevel    | integer                | not null Vorgabewert 0
> Indexe:
>     "measurementstype_pkey" PRIMARY KEY, btree (measurementstypeid)
>     "measurementstype_datatype" btree (datatype)
>     "measurementstype_name" btree (name)
>     "measurementstype_step" btree (step)
>     "measurementstype_stepdescription" btree (stepdescription)
>