Sample Header Ad - 728x90

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