Difference TEXT and fulltext

[SanF Fer]

Given this bit of SQL:

CREATE TABLE products (
id SERIAL8 NOT NULL PRIMARY KEY,
name text NOT NULL,
keywords varchar(64)[],
short_summary phrase,
long_description fulltext,
price bigint,
inventory_count integer,
discontinued boolean default false,
availability_date date
);

What would the effect be of this query; where you run it twice, one where `long_description` is `TEXT`, the other `fulltext`:

zdb('products', products.ctid) ==>'(long_description:"*test*")

My question is specifically: when marking a column to be of the special ZomboDB types, do we get performance gains?

[Eric Ridge]

Hey there!

The difference is that columns of type 'fulltext' (or 'phrase' or 'fulltext_with_shingles') are analyzed by Elasticsearch, whereas all other types (including 'text', 'varchar(n)') are indexed as whole values.

Assume you have a row in Postgres where the value for "long_description" is:  "this string is a test of using ZombodB".

In the case where "long_description" is of type fulltext, you could simply search: ==> 'long_description:test' and find that row.  And this would be extremely fast.

In the case where "long_description" is of type text, you'd have to do the left/right wildcard truncation:  ==> 'long_description:*test*'.  And this would be extremely slow because Elasticsearch would have to scan the entire terms index to do the full sub-string search.  In general, left truncation is a *TERRIBLE* thing to ask Elasticsearch to do.

If you haven't already, read through ZomboDB's type-mapping documentation:  https://github.com/zombodb/zombodb/blob/master/TYPE-MAPPING.md

In general, the column types you chose should, if they're string values, should best represent how you want to be able to search it via Elasticsearch -- either "full, exact value" or individual terms (text v/s fulltext/phrase).

I hope this helps.  If not, please let me know.

eric

--
You received this message because you are subscribed to the Google Groups "ZomboDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to zombodb+u...@googlegroups.com.
To post to this group, send email to zom...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/CAMSoeuTmNJYEtTMTyzrVB_QW__Z44MYa7UwdkUFHhnmFUVOMaw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[sa.fer...@gmail.com]

Ahhh. That clears things up a bit :) Was expecting some more performance out of ES - turns out I was just using it wrong.

Ill make sure to read the rest of the documentation and thanks for the help.

[Eric Ridge]

Cool cool. And please, do not be afraid to ask more questions. I am happy to help!

eric

--
You received this message because you are subscribed to the Google Groups "ZomboDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to zombodb+u...@googlegroups.com.
To post to this group, send email to zom...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/zombodb/6f6cdba2-c2c1-4691-919e-cb29b809c45a%40googlegroups.com.