Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results
2
votes
1
answer
342
views
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](https://dba.stackexchange.com/a/80992/28774) 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);
Asked by Kev
(474 rep)
Oct 23, 2017, 07:20 PM
Last activity: Apr 22, 2025, 07:04 AM
Last activity: Apr 22, 2025, 07:04 AM