On Fri, May 1, 2015 at 07:57:27AM -0400, Robert Haas wrote: > On Thu, Apr 30, 2015 at 9:34 PM, Bruce Momjian <br...@momjian.us> 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 <br...@momjian.us> 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers