I have a query that look like this:
SELECT
DISTINCT "articles"."id",
"articles"."company_uuid",
"articles"."status",
"articles"."discount_reference",
"articles"."created_at"
--- more columns
FROM
"articles"
INNER JOIN "customers" ON "customers"."rec" = 'f'
AND "customers"."article_id" = "articles"."id"
WHERE
"articles"."type" = 'sending_request'
AND "articles"."hidden" = 'f'
AND "articles"."discount_reference" = 'Discount/737251623'
AND "articles"."api_domain" = 'company'
AND "articles"."status" IN ('completed', 'active')
AND (customers.search_text ILIKE unaccent('%verb%'))
ORDER BY
authored_on DESC NULLS LAST
LIMIT
20 OFFSET 0;
The first query is slow, but when I reran it is always faster. I do not see a clear answer when I compare plans. I have a gin trigram index for the customers search text.
https://explain.dalibo.com/plan/b11657f576699fa8
And second run
https://explain.dalibo.com/plan/g81h74b9g521g5e7
Is the difference in the IO & Buffers the source of the difference?
I am running on PostgreSQL 14 on RDS.
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
for your next question. Consider instructions for Postgres performance questions.