Excluding facets - need help

[nc-tech-user <nc...@komputronik.pl>]

​Dear Solr User,

I develop an online shop which has possibility to search for products and filter the results. I've used Solr as an engine to search through our shop offer.

Currently I am trying to rebuild the products filter so it will use Solr search facet result to show its attributes. I am having some difficulties with this solution. In most cases I'm using facet on about 60 fields (their types are float, integer and array of integers). To exclude some filters from facet result I'm using "Tagging and Excluding Filters" solution (https://lucene.apache.org/solr/guide/6_6/faceting.html#Faceting-TaggingandExcludingFilters). It works as I expect, the facet result are being build correctly with correct facet results, but the response from Solr is very slow and becasue of that I cannot deploy this on production (about 3.5 seconds).

Here is an example of the query url:
POST request on http://1.1.1.1:8983/solr/products/select

with parameters:

omitHeader=true&elevateIds=451799,457521,434886,450504,448483&forceElevation=true&wt=json&json.nl=flat&q=*:*&start=0&rows=20&fl=id,[elevated],variant_id:variant_id_i&sort=activity_1_i+desc,flag_w_i+asc,map(variant_id_i,1,1e6,1,0)+desc,score+desc&fq={!collapse+field=variant_id_i+sort="activity_1_i+desc,score+desc,created_at_dt+desc"+nullPolicy=expand+tag=collapse_grouping}&fq={!cache=true+cost=500}category_1_id_is:5022&fq={!cache=true+cost=400}activity_1_i:[*+TO+*]&fq={!tag=attribute_111420_is,base_filters}attribute_111420_is:(97067)&defType=edismax&boost=sum(1,product(def(scale(log(sum(1,popularity_i)),0,1),0),2),product(def(scale(timestamp_price(margin_price_interval_10_gross_s,1530093188),0,1),0),1),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"a0"),0),10)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"a1"),0),9)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"a2"),0),8)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"a3"),0),7)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"a4"),0),6)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"a5"),0),5)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"p"),0),6)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"c"),0),6)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"x"),0),4)),map(availability_storehouse_balance_1_i,0,10,0,product(def(termfreq(product_stock_marker_s,"z"),0),5)))&facet=true&facet.field={!key=storehouse+ex=storehouse_filter}availability_storehouse_1_ss&facet.field={!key=category}category_1_id_is&facet.field={!key=producers+ex=producer_filters}producer_id_i&facet.field={!key=base_producers+ex=base_filters,producer_filters}producer_id_i&facet.field={!key=promotion_labels+ex=promotion_labels_filters}promotion_label_10_is&facet.field={!key=base_promotion_labels+ex=base_filters,promotion_labels_filters}promotion_label_10_is&facet.field={!key=object_types+ex=object_types_filters}object_type_id_i&facet.field={!key=base_object_types+ex=base_filters,object_types_filters}object_type_id_i&facet.field={!key=attribute_1923_select+ex=attribute_1923_is}attribute_1923_is&facet.field={!key=base_attribute_1923_select+ex=base_filters}attribute_1923_is&facet.field={!key=attribute_1924_select+ex=attribute_1924_is}attribute_1924_is&facet.field={!key=base_attribute_1924_select+ex=base_filters}attribute_1924_is&facet.field={!key=attribute_1925_float+ex=attribute_1925_f}attribute_1925_f&facet.field={!key=base_attribute_1925_float+ex=base_filters}attribute_1925_f&facet.field={!key=attribute_1929_float+ex=attribute_1929_f}attribute_1929_f&facet.field={!key=base_attribute_1929_float+ex=base_filters}attribute_1929_f&facet.field={!key=attribute_1930_select+ex=attribute_1930_is}attribute_1930_is&facet.field={!key=base_attribute_1930_select+ex=base_filters}attribute_1930_is&facet.field={!key=attribute_1931_select+ex=attribute_1931_is}attribute_1931_is&facet.field={!key=base_attribute_1931_select+ex=base_filters}attribute_1931_is&facet.field={!key=attribute_1932_float+ex=attribute_1932_f}attribute_1932_f&facet.field={!key=base_attribute_1932_float+ex=base_filters}attribute_1932_f&facet.field={!key=attribute_1933_float+ex=attribute_1933_f}attribute_1933_f&facet.field={!key=base_attribute_1933_float+ex=base_filters}attribute_1933_f&facet.field={!key=attribute_1934_select+ex=attribute_1934_is}attribute_1934_is&facet.field={!key=base_attribute_1934_select+ex=base_filters}attribute_1934_is&facet.field={!key=attribute_1935_select+ex=attribute_1935_is}attribute_1935_is&facet.field={!key=base_attribute_1935_select+ex=base_filters}attribute_1935_is&facet.field={!key=attribute_1936_select+ex=attribute_1936_is}attribute_1936_is&facet.field={!key=base_attribute_1936_select+ex=base_filters}attribute_1936_is&facet.field={!key=attribute_1942_select+ex=attribute_1942_is}attribute_1942_is&facet.field={!key=base_attribute_1942_select+ex=base_filters}attribute_1942_is&facet.field={!key=attribute_1943_select+ex=attribute_1943_is}attribute_1943_is&facet.field={!key=base_attribute_1943_select+ex=base_filters}attribute_1943_is&facet.field={!key=attribute_1944_select+ex=attribute_1944_is}attribute_1944_is&facet.field={!key=base_attribute_1944_select+ex=base_filters}attribute_1944_is&facet.field={!key=attribute_1947_select+ex=attribute_1947_is}attribute_1947_is&facet.field={!key=base_attribute_1947_select+ex=base_filters}attribute_1947_is&facet.field={!key=attribute_1952_float+ex=attribute_1952_f}attribute_1952_f&facet.field={!key=base_attribute_1952_float+ex=base_filters}attribute_1952_f&facet.field={!key=attribute_3256_float+ex=attribute_3256_f}attribute_3256_f&facet.field={!key=base_attribute_3256_float+ex=base_filters}attribute_3256_f&facet.field={!key=attribute_109072_select+ex=attribute_109072_is}attribute_109072_is&facet.field={!key=base_attribute_109072_select+ex=base_filters}attribute_109072_is&facet.field={!key=attribute_109141_select+ex=attribute_109141_is}attribute_109141_is&facet.field={!key=base_attribute_109141_select+ex=base_filters}attribute_109141_is&facet.field={!key=attribute_109179_select+ex=attribute_109179_is}attribute_109179_is&facet.field={!key=base_attribute_109179_select+ex=base_filters}attribute_109179_is&facet.field={!key=attribute_109181_float+ex=attribute_109181_f}attribute_109181_f&facet.field={!key=base_attribute_109181_float+ex=base_filters}attribute_109181_f&facet.field={!key=attribute_109297_bool+ex=attribute_109297_b}attribute_109297_b&facet.field={!key=base_attribute_109297_bool+ex=base_filters}attribute_109297_b&facet.field={!key=attribute_109894_float+ex=attribute_109894_f}attribute_109894_f&facet.field={!key=base_attribute_109894_float+ex=base_filters}attribute_109894_f&facet.field={!key=attribute_111420_select+ex=attribute_111420_is}attribute_111420_is&facet.field={!key=base_attribute_111420_select+ex=base_filters}attribute_111420_is&facet.field={!key=attribute_113491_select+ex=attribute_113491_is}attribute_113491_is&facet.field={!key=base_attribute_113491_select+ex=base_filters}attribute_113491_is&facet.field={!key=attribute_113648_float+ex=attribute_113648_f}attribute_113648_f&facet.field={!key=base_attribute_113648_float+ex=base_filters}attribute_113648_f&facet.field={!key=attribute_113701_select+ex=attribute_113701_is}attribute_113701_is&facet.field={!key=base_attribute_113701_select+ex=base_filters}attribute_113701_is&facet.field={!key=attribute_113781_select+ex=attribute_113781_is}attribute_113781_is&facet.field={!key=base_attribute_113781_select+ex=base_filters}attribute_113781_is&facet.field={!key=attribute_113783_select+ex=attribute_113783_is}attribute_113783_is&facet.field={!key=base_attribute_113783_select+ex=base_filters}attribute_113783_is&facet.field={!key=attribute_113784_select+ex=attribute_113784_is}attribute_113784_is&facet.field={!key=base_attribute_113784_select+ex=base_filters}attribute_113784_is&facet.field={!key=attribute_113792_select+ex=attribute_113792_is}attribute_113792_is&facet.field={!key=base_attribute_113792_select+ex=base_filters}attribute_113792_is&facet.field={!key=attribute_113891_select+ex=attribute_113891_is}attribute_113891_is&facet.field={!key=base_attribute_113891_select+ex=base_filters}attribute_113891_is&facet.field={!key=attribute_113893_select+ex=attribute_113893_is}attribute_113893_is&facet.field={!key=base_attribute_113893_select+ex=base_filters}attribute_113893_is&f.availability_storehouse_1_ss.facet.mincount=1&f.category_1_id_is.facet.mincount=1&f.producer_id_i.facet.limit=-1&f.producer_id_i.facet.limit=-1&f.producer_id_i.facet.mincount=1&f.producer_id_i.facet.mincount=1&f.promotion_label_10_is.facet.limit=-1&f.promotion_label_10_is.facet.limit=-1&f.promotion_label_10_is.facet.mincount=1&f.promotion_label_10_is.facet.mincount=1&f.object_type_id_i.facet.limit=-1&f.object_type_id_i.facet.limit=-1&f.object_type_id_i.facet.mincount=1&f.object_type_id_i.facet.mincount=1&f.attribute_1923_is.facet.mincount=1&f.attribute_1923_is.facet.mincount=1&f.attribute_1924_is.facet.mincount=1&f.attribute_1924_is.facet.mincount=1&f.attribute_1925_f.facet.mincount=1&f.attribute_1925_f.facet.mincount=1&f.attribute_1929_f.facet.mincount=1&f.attribute_1929_f.facet.mincount=1&f.attribute_1930_is.facet.mincount=1&f.attribute_1930_is.facet.mincount=1&f.attribute_1931_is.facet.mincount=1&f.attribute_1931_is.facet.mincount=1&f.attribute_1932_f.facet.mincount=1&f.attribute_1932_f.facet.mincount=1&f.attribute_1933_f.facet.mincount=1&f.attribute_1933_f.facet.mincount=1&f.attribute_1934_is.facet.mincount=1&f.attribute_1934_is.facet.mincount=1&f.attribute_1935_is.facet.mincount=1&f.attribute_1935_is.facet.mincount=1&f.attribute_1936_is.facet.mincount=1&f.attribute_1936_is.facet.mincount=1&f.attribute_1942_is.facet.mincount=1&f.attribute_1942_is.facet.mincount=1&f.attribute_1943_is.facet.mincount=1&f.attribute_1943_is.facet.mincount=1&f.attribute_1944_is.facet.mincount=1&f.attribute_1944_is.facet.mincount=1&f.attribute_1947_is.facet.mincount=1&f.attribute_1947_is.facet.mincount=1&f.attribute_1952_f.facet.mincount=1&f.attribute_1952_f.facet.mincount=1&f.attribute_3256_f.facet.mincount=1&f.attribute_3256_f.facet.mincount=1&f.attribute_109072_is.facet.mincount=1&f.attribute_109072_is.facet.mincount=1&f.attribute_109141_is.facet.mincount=1&f.attribute_109141_is.facet.mincount=1&f.attribute_109179_is.facet.mincount=1&f.attribute_109179_is.facet.mincount=1&f.attribute_109181_f.facet.mincount=1&f.attribute_109181_f.facet.mincount=1&f.attribute_109297_b.facet.mincount=1&f.attribute_109297_b.facet.mincount=1&f.attribute_109894_f.facet.mincount=1&f.attribute_109894_f.facet.mincount=1&f.attribute_111420_is.facet.mincount=1&f.attribute_111420_is.facet.mincount=1&f.attribute_113491_is.facet.mincount=1&f.attribute_113491_is.facet.mincount=1&f.attribute_113648_f.facet.mincount=1&f.attribute_113648_f.facet.mincount=1&f.attribute_113701_is.facet.mincount=1&f.attribute_113701_is.facet.mincount=1&f.attribute_113781_is.facet.mincount=1&f.attribute_113781_is.facet.mincount=1&f.attribute_113783_is.facet.mincount=1&f.attribute_113783_is.facet.mincount=1&f.attribute_113784_is.facet.mincount=1&f.attribute_113784_is.facet.mincount=1&f.attribute_113792_is.facet.mincount=1&f.attribute_113792_is.facet.mincount=1&f.attribute_113891_is.facet.mincount=1&f.attribute_113891_is.facet.mincount=1&f.attribute_113893_is.facet.mincount=1&f.attribute_113893_is.facet.mincount=1

[cid:a6df87d6-3ea0-49fc-8bfd-5b6dda0030fb]

I would appreciate any hints from you. Did I use appropriate Solr functionality ? Did I use it correctly ? Is there any other way we can have the same results but in shorter response time ?


Thanks in advance,


nc-tech-user

Re: Excluding facets - need help

[Shawn Heisey <ap...@elyograg.org>]

On 6/27/2018 6:14 AM, nc-tech-user wrote:
>
> Currently I am trying to rebuild the products filter so it will use 
> Solr search facet result to show its attributes. I am having some 
> difficulties with this solution. In most cases I'm using facet on 
> about 60 fields (their types are float, integer and array of 
> integers). To exclude some filters from facet result I'm using 
> "Tagging and Excluding Filters" solution 
> (https://lucene.apache.org/solr/guide/6_6/faceting.html#Faceting-TaggingandExcludingFilters 
> <https://lucene.apache.org/solr/guide/6_6/faceting.html#Faceting-TaggingandExcludingFilters>). 
> It works as I expect, the facet result are being build correctly with 
> correct facet results, but the response from Solr is very slow and 
> becasue of that I cannot deploy this on production (about 3.5 seconds).
>

I'm going to trim out your actual query.

That is a LOT of facets.  I count "facet.field" in the query 74 times.

You've given Solr a rather large amount of work to do, even if the index 
isn't very big.  If the index is big, the amount of work is HUGE.

Faceting can be expedited by having docValues on the field.  This does 
increase the index size, which can by itself reduce overall Solr 
performance.  I would strongly recommend that you add docValues to any 
field you're using for facets.  A complete reindex is required after 
adding docValues to the field definition.

The speed of facets is dramatically slower if the field you're faceting 
on has a very large number of unique values.  This is known as 
"cardinality" in database circles.

https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)

It might not be possible to gain much performance on a query with 74 
facets in it.  If I were to get a time of 3.5 seconds for a query on my 
indexes with 74 facets, I'd be extremely happy.  I often see query times 
much larger than this with NO facets.  This happens when you've got 
nearly 200 million documents in the index.

One of the most common reasons for slow query performance on Solr is 
insufficient system memory.  If you tell me what OS you have Solr 
running on, I can give you some steps for gathering information that 
will allow me to make a guess about whether your system's memory is 
sufficient for what you're asking Solr to do.

I have no idea how the use of tagging and excluding affects performance.

One thing to note: It looks like you're attempting to turn some of your 
filters into PostFilters by using the cost attribute.  You should know 
that the lucene query parser (which is default) does not support the 
PostFilter interface.  So changing the cost of those filters is probably 
not doing what you've planned.  Also, once you are using a query parser 
that supports PostFilter, cache must be false, or it's not a PostFilter.

A query that has many filter queries can also be quite slow, especially 
if the filterCache is not getting a high hitcount.

Thanks,
Shawn