On 09/25/2017 08:10 PM, Дилян Палаузов wrote:
> Hello,
>
> for the record, in this table
>
> spamassassin=> \d bayes_token
> Table "public.bayes_token"
> Column Type Modifiers
> ────────── ─────── ────────────────────────────
> id integer not null default 0
> token bytea not null default '\x'::bytea
> spam_count integer not null default 0
> ham_count integer not null default 0
> atime integer not null default 0
> Indexes:
> "bayes_token_pkey" PRIMARY KEY, btree (id, token)
> "bayes_token_idx1" btree (token)
>
>
> "SELECT id, token FROM bayes_token" and "SELECT id, token FROM
> bayes_token WHERE id > 0" do Seq Scan and need 35-50 Sec.
>
> But "SELECT id, token FROM bayes_token WHERE id > 1" uses Index Only
> Scan and 0.04 seconds on pg 9.6.5 .
>
I don't quite see how this is related to the original topic in this
thread, which was discussing DISTINCT ON vs. indexes.
But more importantly, it's really difficult to give you answers without
you showing us EXPLAIN (and ideally EXPLAIN ANALYZE) for the queries.
FWIW, my guess is that there are many rows with (id > 0), and scanning
them using index only scan would be expensive. While there are only very
few rows with (id > 1), so the database uses IOS.
You can try pushing the database towards IOS by disabling sequential and
bitmap scans.
set enable_seqscan = off;set enable_bitmapscan = off;
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs