| From: | Fabio Ugo Venchiarutti <fabio(at)vuole(dot)me> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Unexpected planner behavior with *_pattern_ops index matching | 
| Date: | 2014-10-30 06:12:20 | 
| Message-ID: | 5451D6C4.7040308@vuole.me | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Greetings.
I'm in the process of indexing a virtual file system (on 9.2.9, build 
info below) and I ran into what I perceive as an inconsistency in the 
way index viability is assessed by the planner.
Perhaps I'm misinterpreting the docs, but it seems like stable functions 
don't behave as per 
http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html when 
*_pattern_ops operator classes are used (not sure about others).
Steps I followed to reproduce the anomaly:
geoop_prototype=# -- Build/platform Info:
geoop_prototype=# SELECT version();
                                                    version 
--------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.2.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 
4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)
geoop_prototype=# 
----------------------------------------------------------------
geoop_prototype=#
geoop_prototype=# -- Small sample of data volume/distribution from the 
involved table and column.
geoop_prototype=# SELECT COUNT(0) FROM inode_segments WHERE (full_path 
IS NOT NULL);
  count
--------
  291019
(1 row)
geoop_prototype=# SELECT char_length(full_path) FROM inode_segments 
WHERE (full_path IS NOT NULL) ORDER BY random() LIMIT 10;
  char_length
-------------
           80
          126
          108
           75
          116
           71
           70
           76
          137
          113
(10 rows)
geoop_prototype=# 
----------------------------------------------------------------
geoop_prototype=#
geoop_prototype=# -- Query plan without the operator class-specific 
index. As expected the left anchored regex prompts a table scan
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE 
full_path ~ '^/THIS/MATCHES/NOTHING/';
                               QUERY PLAN
-----------------------------------------------------------------------
  Seq Scan on "inode_segments"  (cost=0.00..27401.85 rows=29 width=8)
    Filter: (("full_path")::"text" ~ '^/THIS/MATCHES/NOTHING/'::"text")
(2 rows)
geoop_prototype=# 
----------------------------------------------------------------
geoop_prototype=#
geoop_prototype=# -- We now define an ad-hoc index
geoop_prototype=# CREATE INDEX ix_inode_segments_filter_by_subtree ON 
gorfs.inode_segments USING BTREE(full_path varchar_pattern_ops);
CREATE INDEX
geoop_prototype=# 
----------------------------------------------------------------
geoop_prototype=#
geoop_prototype=# -- Same query as above. Predictably, the index is now 
being scanned instead
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE 
full_path ~ '^/THIS/MATCHES/NOTHING/';
 
QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using "ix_inode_segments_filter_by_subtree" on 
"inode_segments"  (cost=0.00..8.49 rows=29 width=8)
    Index Cond: ((("full_path")::"text" ~>=~ 
'/THIS/MATCHES/NOTHING/'::"text") AND (("full_path")::"text" ~<~ 
'/THIS/MATCHES/NOTHING0'::"text"))
    Filter: (("full_path")::"text" ~ '^/THIS/MATCHES/NOTHING/'::"text")
(3 rows)
geoop_prototype=# 
----------------------------------------------------------------
geoop_prototype=#
geoop_prototype=# -- However, when the comparison value comes from a 
function that is marked as STABLE, the planner reverts to a full scan
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE 
full_path ~ CONCAT('^/THIS/MATCHES/NOTHING/');
                                QUERY PLAN
-------------------------------------------------------------------------
  Seq Scan on "inode_segments"  (cost=0.00..28789.02 rows=29 width=8)
    Filter: (("full_path")::"text" ~ "concat"('^/THIS/MATCHES/NOTHING/'))
(2 rows)
geoop_prototype=# 
----------------------------------------------------------------
geoop_prototype=#
geoop_prototype=# -- Immutable functions are not affected...
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE 
full_path ~ UPPER('^/THIS/MATCHES/NOTHING/');
 
QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------------------------
  Index Scan using "ix_inode_segments_filter_by_subtree" on 
"inode_segments"  (cost=0.00..8.49 rows=29 width=8)
    Index Cond: ((("full_path")::"text" ~>=~ 
'/THIS/MATCHES/NOTHING/'::"text") AND (("full_path")::"text" ~<~ 
'/THIS/MATCHES/NOTHING0'::"text"))
    Filter: (("full_path")::"text" ~ '^/THIS/MATCHES/NOTHING/'::"text")
(3 rows)
----------------------------------------------------------------
geoop_prototype=# -- ... nor are other operator classes (with 
preexisting index. Note that CONCAT is again being used here)
geoop_prototype=# EXPLAIN SELECT st_ino FROM inode_segments WHERE 
full_path > CONCAT('/THIS/MATCHES/NOTHINA/');
                                                QUERY PLAN 
---------------------------------------------------------------------------------------------------------
  Index Scan using "uc_no_duplicate_full_paths" on "inode_segments" 
(cost=0.00..167.32 rows=418 width=8)
    Index Cond: (("full_path")::"text" > '/THIS/MATCHES/NOTHINA/'::"text")
(2 rows)
As you can see, CONCAT()'s output isn't deemed suitable for an index 
scan. The same happens for all type-compatible STABLE functions i tried.
Am I missing something here?
TIA and Regards
Fabio Venchiarutti
| From | Date | Subject | |
|---|---|---|---|
| Next Message | VENKTESH GUTTEDAR | 2014-10-30 06:26:02 | Re: Appending new data to existing field of Json data type | 
| Previous Message | Jorge Arevalo | 2014-10-29 20:26:01 | Re: Query optimization |