CODEWITHSUNDEEP
postgresqlindexingexplain
Jscore
Jscore

Reputation: 405

Why is my Postgresql instance not using bitmap index scanning?

I have two instances of Postgresql running, one which is on my local machine running version 10.5 and the other version on my production machine running 9.5.10. I create the tables on my local machine and move them to the production machine using pg_dump and pr_restore. When I run the following command on my local machine it takes about 100 msec:

CREATE TABLE test_point AS
    SELECT a.*, b.*, a.total_score + b.total_score_table_2 AS total_score_all
    FROM master_enigma_table_designations b,
      ST_Transform(ST_SetSRID(ST_Makepoint(-408601.4826183041,6707237.695265564), 3857), 27700) dropped_pin LEFT JOIN
    master_enigma_table a
    ON ST_Within(dropped_pin, a.wkb_geometry)
    WHERE a.poly_id = b.poly_id_new;

and when I run EXPLAIN ANALYZE I get the following output:

"Nested Loop  (cost=1119.13..180619.12 rows=9594 width=4224) (actual time=0.157..0.225 rows=1 loops=1)"
"  Buffers: shared hit=22"
"  ->  Nested Loop  (cost=1118.69..118339.83 rows=9594 width=2444) (actual time=0.126..0.189 rows=1 loops=1)"
"        Buffers: shared hit=18"
"        ->  Function Scan on dropped_pin  (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.006 rows=1 loops=1)"
"        ->  Bitmap Heap Scan on master_enigma_table a  (cost=1118.69..118243.88 rows=9594 width=2444) (actual time=0.108..0.167 rows=1 loops=1)"
"              Recheck Cond: (wkb_geometry ~ dropped_pin.dropped_pin)"
"              Filter: _st_contains(wkb_geometry, dropped_pin.dropped_pin)"
"              Rows Removed by Filter: 2"
"              Heap Blocks: exact=3"
"              Buffers: shared hit=18"
"              ->  Bitmap Index Scan on master_enigma_table_gist_index  (cost=0.00..1116.29 rows=28783 width=0) (actual time=0.089..0.090 rows=3 loops=1)"
"                    Index Cond: (wkb_geometry ~ dropped_pin.dropped_pin)"
"                    Buffers: shared hit=8"
"  ->  Index Scan using master_enigma_table_designations_poly_id on master_enigma_table_designations b  (cost=0.44..6.48 rows=1 width=1772) (actual time=0.021..0.024 rows=1 loops=1)"
"        Index Cond: (poly_id_new = a.poly_id)"
"        Buffers: shared hit=4"
"Planning time: 1.397 ms"
"Execution time: 10.058 ms"

When I run the exact same query on my production machine it takes 8 minutes. When I run EXPLAIN ANALYZE I get:

"Nested Loop  (cost=0.44..15399024.56 rows=9594 width=4208) (actual time=326842.620..478541.379 rows=1 loops=1)"
"  Buffers: shared hit=1314092 read=6890152"
"  ->  Nested Loop  (cost=0.00..15323938.18 rows=9594 width=2425) (actual time=326842.576..478541.332 rows=1 loops=1)"
"        Join Filter: ((a.wkb_geometry ~ dropped_pin.dropped_pin) AND _st_contains(a.wkb_geometry, dropped_pin.dropped_pin))"
"        Rows Removed by Join Filter: 28783093"
"        Buffers: shared hit=1314088 read=6890152"
"        ->  Function Scan on dropped_pin  (cost=0.00..0.01 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)"
"        ->  Seq Scan on master_enigma_table a  (cost=0.00..7768445.30 rows=28782830 width=2425) (actual time=0.018..458071.770 rows=28783094 loops=1)"
"              Buffers: shared hit=590465 read=6890152"
"  ->  Index Scan using master_enigma_table_designations_new_poly_id on master_enigma_table_designations b  (cost=0.44..7.81 rows=1 width=1783) (actual time=0.012..0.013 rows=1 loops=1)"
"        Index Cond: (poly_id_new = a.poly_id)"
"        Buffers: shared hit=4"
"Planning time: 26.628 ms"
"Execution time: 478582.199 ms"

It appears that my production machine is not using Bitmap Index Scanning, whereas my local machine is. Both instances have the same tables, indexes and I have run ANALYZE on all the tables. I have run SHOW ALL and bitmap scanning is set too on.

Does anyone have any suggestions as to what I can do to solve my problem.

Upvotes: 1

Views: 200

Answers (1)

Grzegorz Grabek
Grzegorz Grabek

Reputation: 980

A different version of Postgres means you use a different version of PostGIS also. The old one with 9.5 is not using an index on st_within() function. In 9.5 you have to add st_dwithin(dropped_pin, a.wkb_geometry,0) to use indexed values.

CREATE TABLE test_point AS
SELECT a.*, b.*, a.total_score + b.total_score_table_2 AS total_score_all
  FROM master_enigma_table_designations b,
       ST_Transform(ST_SetSRID(ST_Makepoint(-408601.4826183041,6707237.695265564), 3857), 27700) dropped_pin 
  LEFT JOIN master_enigma_table a ON ST_Within(dropped_pin, a.wkb_geometry)
                                  and ST_DWithin(dropped_pin, a.wkb_geometry,0)
WHERE a.poly_id = b.poly_id_new;

Simple advice - use the same version of Postgres and PostGIS on both machines or you will experience more of such incompatibility issues.

Upvotes: 1

Related Questions