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
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 |