1

Table a has > 3 million rows and this index:

CREATE INDEX idx_a_txt
  ON a
  USING gin
  (txt);

This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I search for something that does:

SELECT *
FROM a
WHERE a.txt @@ to_tsquery('simple_english', $1)
LIMIT 10

The query plan:

Limit  (cost=0.00..847.17 rows=10 width=1285)
->  Seq Scan on a  (cost=0.00..560995.49 rows=6622 width=1285)
        Filter: (txt @@ '''test'''::tsquery)

If I leave off the LIMIT 10, the results are reversed: In the non-existing case, it takes 12 milliseconds, but in the existing case, it takes > 30 seconds (naturally, because it's trying to return 50,000 rows.) Anyway, the query plan instead is:

Bitmap Heap Scan on a  (cost=1839.32..26016.86 rows=6622 width=1285)
Recheck Cond: (txt @@ '''blahblahblah'''::tsquery)
->  Bitmap Index Scan on idx_a_txt  (cost=0.00..1837.67 rows=6622 width=0)
        Index Cond: (txt @@ '''blahblahblah'''::tsquery)

I've run ANALYZE on table a as suggested here but the results are still as above.

If I instead SELECT COUNT(*) I get 50000 back in 150 milliseconds, or 0 back in 12 milliseconds.

Is there any way to make such a query work quickly whether there are results or not?

Reproducible example

Not quite the 5s, but 2s still vs 12-31ms, so you get the idea, at least:

CREATE TEXT SEARCH DICTIONARY simple_english (
   TEMPLATE = simple,
   stopwords = 'english'
);
CREATE TEXT SEARCH CONFIGURATION simple_english (
  PARSER = "default"
);
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR asciihword WITH simple_english;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR asciiword WITH simple_english;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR email WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR file WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR float WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR host WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR hword WITH simple_english;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR hword_asciipart WITH simple_english;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR hword_numpart WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR hword_part WITH simple_english;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR int WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR numhword WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR numword WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR sfloat WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR uint WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR url WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR url_path WITH simple;
ALTER TEXT SEARCH CONFIGURATION simple_english ADD MAPPING FOR version WITH simple;

drop table if exists a;
create table a (id text,
  b text NOT NULL,
  c text,
  d timestamp with time zone NOT NULL,
  e text NOT NULL,
  f timestamp with time zone,
  g timestamp with time zone,
  h integer NOT NULL DEFAULT 0,
  i integer NOT NULL DEFAULT 0,
  j text,
  k text,
  l text,
  m text,
  n timestamp with time zone,
  o text NOT NULL,
  p text,
  q timestamp with time zone NOT NULL,
  r timestamp with time zone NOT NULL,
  s text,
  t character varying(5) NOT NULL DEFAULT 'fb'::character varying,
  txt tsvector
);
CREATE INDEX idx_a_txt
  ON a
  USING gin
  (txt);
insert into a (id, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, txt)
SELECT uuid_in(md5(random()::text || now()::text)::cstring), 'foobar', 'foobar2', now(), md5(random()::text), now(), now(), random()*100, random()*100, random()::text, random()::text || ' ' || random()::text, random()::text || ' ' || random()::text, '', null, (random()*100000)::text, '', now(), now(), '', 'fb', to_tsvector('simple_english', md5(random()::text))
FROM generate_series(1,3400000);
0

This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I search for something that does:

Well of course, you have LIMIT 10. In the event you have something you can stop searching immediately. In the event you do not find 10 things, you have to search the entire index or table.

  • This makes sense, except that searching an index shouldn't take 5 seconds. If I use select count(id), for example, I get 0 back in 12 ms, or for something that exists, 50000 comes back in 162 ms. – Kev Oct 23 '17 at 20:39
  • @Kev no idea what you're doing perhaps you can extract out a test case? – Evan Carroll Oct 23 '17 at 20:41
  • The OP was already distilled, but I don't have any generated test data on hand. I'll try to make some and get back to you. Meanwhile I added the above details to the question. – Kev Oct 23 '17 at 20:44
  • Thanks for the suggestion, I've added an approximation of the table with random data. It performs a better than real life, but the orders of magnitude are the same. – Kev Oct 24 '17 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.