Index usage for tag queries without values

[pnorman]

https://github.com/iandees/xapi-servlet/blob/master/src/main/java/com/yellowbkpk/geo/xapi/db/Selector.java#L37 builds a query string using exist(tags, key) but this does not use the GIN index set up in the documentation so ends up doing a sequential scan. A query with tags ? key will use the index.

In this example the cost is 21 times greater for the exist() query.

xapi=# EXPLAIN (FORMAT YAML) SELECT * FROM ways WHERE tags ? 'source_1';
                   QUERY PLAN
-------------------------------------------------
 - Plan:                                        +
     Node Type: "Bitmap Heap Scan"              +
     Relation Name: "ways"                      +
     Alias: "ways"                              +
     Startup Cost: 4541.63                      +
     Total Cost: 551334.20                      +
     Plan Rows: 151436                          +
     Plan Width: 1167                           +
     Recheck Cond: "(tags ? 'source_1'::text)"  +
     Plans:                                     +
       - Node Type: "Bitmap Index Scan"         +
         Parent Relationship: "Outer"           +
         Index Name: "idx_ways_tags"            +
         Startup Cost: 0.00                     +
         Total Cost: 4503.77                    +
         Plan Rows: 151436                      +
         Plan Width: 0                          +
         Index Cond: "(tags ? 'source_1'::text)"
(1 row)

xapi=# EXPLAIN (FORMAT YAML) SELECT * FROM ways WHERE exist(tags,'source_1');
                 QUERY PLAN
---------------------------------------------
 - Plan:                                    +
     Node Type: "Seq Scan"                  +
     Relation Name: "ways"                  +
     Alias: "ways"                          +
     Startup Cost: 0.00                     +
     Total Cost: 11578309.22                +
     Plan Rows: 50478774                    +
     Plan Width: 1167                       +
     Filter: "exist(tags, 'source_1'::text)"
(1 row)

[iandees]

Good catch. I thought that since they performed the same function they'd use the same index. Definitely worth changing.

[pnorman]

Some other queries finished and in the case of source_1 it's actually a 14257:1 speed difference. 21:1 would be for an 'average' tag. I'd fix it, but I'm not sure how to escape the ? properly.

xapi=# EXPLAIN (ANALYZE, FORMAT YAML) SELECT * FROM ways WHERE tags ? 'source_1';
                   QUERY PLAN
-------------------------------------------------
 - Plan:                                        +
     Node Type: "Bitmap Heap Scan"              +
     Relation Name: "ways"                      +
     Alias: "ways"                              +
     Startup Cost: 4613.64                      +
     Total Cost: 551409.83                      +
     Plan Rows: 151437                          +
     Plan Width: 1167                           +
     Actual Startup Time: 96.856                +
     Actual Total Time: 812.111                 +
     Actual Rows: 2439                          +
     Actual Loops: 1                            +
     Recheck Cond: "(tags ? 'source_1'::text)"  +
     Plans:                                     +
       - Node Type: "Bitmap Index Scan"         +
         Parent Relationship: "Outer"           +
         Index Name: "idx_ways_tags"            +
         Startup Cost: 0.00                     +
         Total Cost: 4575.78                    +
         Plan Rows: 151437                      +
         Plan Width: 0                          +
         Actual Startup Time: 79.157            +
         Actual Total Time: 79.157              +
         Actual Rows: 2439                      +
         Actual Loops: 1                        +
         Index Cond: "(tags ? 'source_1'::text)"+
   Triggers:                                    +
   Total Runtime: 813.328
(1 row)

xapi=# EXPLAIN (ANALYZE, FORMAT YAML) SELECT * FROM ways WHERE exist(tags,'source_1');
                 QUERY PLAN
---------------------------------------------
 - Plan:                                    +
     Node Type: "Seq Scan"                  +
     Relation Name: "ways"                  +
     Alias: "ways"                          +
     Startup Cost: 0.00                     +
     Total Cost: 11578388.12                +
     Plan Rows: 50479118                    +
     Plan Width: 1167                       +
     Actual Startup Time: 76137.704         +
     Actual Total Time: 638437.979          +
     Actual Rows: 2439                      +
     Actual Loops: 1                        +
     Filter: "exist(tags, 'source_1'::text)"+
   Triggers:                                +
   Total Runtime: 638438.562
(1 row)

[iandees]

Have you tried recompiling with the ?? I don't the execute() calls I'm making try to replace the question mark with a arg.

[pnorman]

I also don't have a setup right now where I can easily compile java