Unexpected planner behavior with *_pattern_ops index matching

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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