Stefan, See below > -----Original Message----- > From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql- > performance-owner@xxxxxxxxxxxxxx] On Behalf Of Weinzierl Stefan > Sent: Thursday, June 05, 2014 3:36 PM > To: pgsql-performance@xxxxxxxxxxxxxx > Subject: [PERFORM] 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 measurementstype t on > m.measurementstypeid=t.measurementstypeid where p.timestamp > between '2013-02-01 15:00:00' and '2013-02-05 21: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=5404751 loops=1) > > -> Bitmap Heap Scan on product p (cost=8279.03..662659.76 > rows=526094 width=8) (actual time=75.903..326.850 rows=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.050 > 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 without tim > > -> Index Scan using measurements_productid on measurements m > (cost=0.58..347.12 rows=3214 width=12) (actual time=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 measurementstype t on > m.measurementstypeid=t.measurementstypeid where p.timestamp > between '2013-02-01 15:00:00' and '2013-02-05 21: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=5404751 loops=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=5404751 loops=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.045 > rows=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.482 rows=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) (actual time=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=16554 loops=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 DELETE SET NULL > > "fk_ordermaterialsid" FOREIGN KEY (ordermaterialsid) REFERENCES > ordermaterials(ordermaterialsid) ON UPDATE CASCADE ON 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) > > > You don't tell: - what kind of hardware (specifically, how much RAM) you are using - what are your config settings: shared_buffers, work_mem, effective_cache_size All this affects planner decisions, when choosing one (or another) execution path/plan. Regards, Igor Neyman
