On Tue, Dec 20, 2011 at 07:08, Tom Lane wrote:
> it'd likely be better if this code ignored unrecognized qual expression
> types rather than Assert'ing they're not there.
The patch replaced that Assert with an elog(ERROR)
> Hmm. I am reminded of how utterly unreadable "diff -u" format is for
> anything longer than single-line changes :-( ...
Sorry, the new patch is in context (-C) diff format proper. I also
moved around code a bit and removed an unused variable that was left
around from the refactoring.
> but I think I don't
> like this refactoring much. Will take a closer look tomorrow.
I was afraid you'd say that, especially for a change that should be
backpatched. But I couldn't think of alternative ways to do it that
give non-bogus estimates.
While writing this patch, the largest dilemma was where to account for
the multiple array scans. Given that this code is mostly a heuristic
and I lack a deep understanding of GIN indexes, it's likely that I got
this part wrong.
Currently I'm doing this:
partialEntriesInQuals *= array_scans;
exactEntriesInQuals *= array_scans;
searchEntriesInQuals *= array_scans;
Which seems to be the right thing as far as random disk accesses are
concerned (successive scans are more likely to hit the cache) and also
works well with queries that don't touch most of the index. But this
fails spectacularly when multiple full scans are performed e.g. LIKE
ANY ('{%,%,%}'). Because index_pages_fetched() ends up removing all of
the rescan costs.
Another approach is multiplying the total cost from the number of
scans. This overestimates random accesses from rescans, but fixes the
above case:
*indexTotalCost = (*indexStartupCost + dataPagesFetched *
spc_random_page_cost) * array_scans;
Regards,
Marti
diff --git a/contrib/pg_trgm/expected/pg_trgm.out b/contrib/pg_trgm/expected/pg_trgm.out
new file mode 100644
index e7af7d4..250d853
*** a/contrib/pg_trgm/expected/pg_trgm.out
--- b/contrib/pg_trgm/expected/pg_trgm.out
*** explain (costs off)
*** 3486,3491
--- 3486,3501
Index Cond: (t ~~* '%BCD%'::text)
(4 rows)
+ explain (costs off)
+ select * from test2 where t like any ('{%bcd%,qua%}');
+QUERY PLAN
+ -
+ Bitmap Heap Scan on test2
+Recheck Cond: (t ~~ ANY ('{%bcd%,qua%}'::text[]))
+-> Bitmap Index Scan on test2_idx_gin
+ Index Cond: (t ~~ ANY ('{%bcd%,qua%}'::text[]))
+ (4 rows)
+
select * from test2 where t like '%BCD%';
t
---
*** select * from test2 where t ilike 'qua%'
*** 3509,3514
--- 3519,3531
quark
(1 row)
+ select * from test2 where t like any ('{%bcd%,qua%}');
+t
+
+ abcdef
+ quark
+ (2 rows)
+
drop index test2_idx_gin;
create index test2_idx_gist on test2 using gist (t gist_trgm_ops);
set enable_seqscan=off;
*** explain (costs off)
*** 3528,3533
--- 3545,3560
Index Cond: (t ~~* '%BCD%'::text)
(2 rows)
+ explain (costs off)
+ select * from test2 where t like any ('{%bcd%,qua%}');
+QUERY PLAN
+ -
+ Bitmap Heap Scan on test2
+Recheck Cond: (t ~~ ANY ('{%bcd%,qua%}'::text[]))
+-> Bitmap Index Scan on test2_idx_gist
+ Index Cond: (t ~~ ANY ('{%bcd%,qua%}'::text[]))
+ (4 rows)
+
select * from test2 where t like '%BCD%';
t
---
*** select * from test2 where t ilike 'qua%'
*** 3551,3553
--- 3578,3587
quark
(1 row)
+ select * from test2 where t like any ('{%bcd%,qua%}');
+t
+
+ abcdef
+ quark
+ (2 rows)
+
diff --git a/contrib/pg_trgm/sql/pg_trgm.sql b/contrib/pg_trgm/sql/pg_trgm.sql
new file mode 100644
index ea902f6..ac969e6
*** a/contrib/pg_trgm/sql/pg_trgm.sql
--- b/contrib/pg_trgm/sql/pg_trgm.sql
*** explain (costs off)
*** 47,56
--- 47,59
select * from test2 where t like '%BCD%';
explain (costs off)
select * from test2 where t ilike '%BCD%';
+ explain (costs off)
+ select * from test2 where t like any ('{%bcd%,qua%}');
select * from test2 where t like '%BCD%';
select * from test2 where t like '%bcd%';
select * from test2 where t ilike '%BCD%';
select * from test2 where t ilike 'qua%';
+ select * from test2 where t like any ('{%bcd%,qua%}');
drop index test2_idx_gin;
create index test2_idx_gist on test2 using gist (t gist_trgm_ops);
set enable_seqscan=off;
*** explain (costs off)
*** 58,64
--- 61,70
select * from test2 where t like '%BCD%';
explain (costs off)
select * from test2 where t ilike '%BCD%';
+ explain (costs off)
+ select * from test2 where t like any ('{%bcd%,qua%}');
select * from test2 where t like '%BCD%';
select * from test2 where t like '%bcd%';
select * from test2 where t ilike '%BCD%';
select