On Fri, May 1, 2015 at 07:57:27AM -0400, Robert Haas wrote:
> On Thu, Apr 30, 2015 at 9:34 PM, Bruce Momjian <[email protected]> wrote:
> > On Wed, Mar 11, 2015 at 02:40:16PM +0000, Andrew Gierth wrote:
> >> An issue that comes up regularly on IRC is that text search queries,
> >> especially on relatively modest size tables or for relatively
> >> non-selective words, often misplan as a seqscan based on the fact that
> >> to_tsvector has procost=1.
> >>
> >> Clearly this cost number is ludicrous.
> >>
> >> Getting the right cost estimate would obviously mean taking the cost of
> >> detoasting into account, but even without doing that, there's a strong
> >> argument that it should be increased to at least the order of 100.
> >> (With the default cpu_operator_cost that would make each to_tsvector
> >> call cost 0.25.)
> >>
> >> (The guy I was just helping on IRC was seeing a slowdown of 100x from a
> >> seqscan in a query that selected about 50 rows from about 500.)
> >
> > Where are we on setting increasing procost for to_tsvector?
>
> We're waiting for you to commit the patch.
OK, I have to write the patch first, so patch attached, using the cost
of 10. I assume to_tsvector() is the ony one needing changes. The
patch will require a catalog bump too.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index 55c246e..72d8c8c
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DESCR("generate headline");
*** 4653,4665 ****
DATA(insert OID = 3755 ( ts_headline PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 25 "25 3615" _null_ _null_ _null_ _null_ _null_ ts_headline _null_ _null_ _null_ ));
DESCR("generate headline");
! DATA(insert OID = 3745 ( to_tsvector PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3614 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsvector_byid _null_ _null_ _null_ ));
DESCR("transform to tsvector");
DATA(insert OID = 3746 ( to_tsquery PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsquery_byid _null_ _null_ _null_ ));
DESCR("make tsquery");
DATA(insert OID = 3747 ( plainto_tsquery PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ plainto_tsquery_byid _null_ _null_ _null_ ));
DESCR("transform to tsquery");
! DATA(insert OID = 3749 ( to_tsvector PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3614 "25" _null_ _null_ _null_ _null_ _null_ to_tsvector _null_ _null_ _null_ ));
DESCR("transform to tsvector");
DATA(insert OID = 3750 ( to_tsquery PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3615 "25" _null_ _null_ _null_ _null_ _null_ to_tsquery _null_ _null_ _null_ ));
DESCR("make tsquery");
--- 4653,4665 ----
DATA(insert OID = 3755 ( ts_headline PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 25 "25 3615" _null_ _null_ _null_ _null_ _null_ ts_headline _null_ _null_ _null_ ));
DESCR("generate headline");
! DATA(insert OID = 3745 ( to_tsvector PGNSP PGUID 12 10 0 0 0 f f f f t f i 2 0 3614 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsvector_byid _null_ _null_ _null_ ));
DESCR("transform to tsvector");
DATA(insert OID = 3746 ( to_tsquery PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ to_tsquery_byid _null_ _null_ _null_ ));
DESCR("make tsquery");
DATA(insert OID = 3747 ( plainto_tsquery PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3615 "3734 25" _null_ _null_ _null_ _null_ _null_ plainto_tsquery_byid _null_ _null_ _null_ ));
DESCR("transform to tsquery");
! DATA(insert OID = 3749 ( to_tsvector PGNSP PGUID 12 10 0 0 0 f f f f t f s 1 0 3614 "25" _null_ _null_ _null_ _null_ _null_ to_tsvector _null_ _null_ _null_ ));
DESCR("transform to tsvector");
DATA(insert OID = 3750 ( to_tsquery PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 3615 "25" _null_ _null_ _null_ _null_ _null_ to_tsquery _null_ _null_ _null_ ));
DESCR("make tsquery");
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
