Query planner wants to use seq scan

Hi all,

We have a slow query. After analyzing, the planner decision seems to be
discutable : the query is faster when disabling seqscan. See below the two
query plan, and an extract from pg_stats.

Any idea about what to change to help the planner ?

An information which can be useful : the number on distinct value on
organization_id is very very low, may be the planner does not known that,
and take the wrong decision.

Regards,

Bertrand

# explain analyze SELECT 1 AS one FROM "external_sync_messages" WHERE
"external_sync_messages"."organization_id" = 1612 AND
("external_sync_messages"."status" NOT IN ('sent_to_proxy', 'in_progress',
'ok')) AND "external_sync_messages"."handled_by" IS NULL LIMIT 1;

QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------

Limit (cost=0.00..12.39 rows=1 width=0) (actual time=232.212..232.213
rows=1 loops=1)

-> Seq Scan on external_sync_messages (cost=0.00..79104.69 rows=6385
width=0) (actual time=232.209..232.209 rows=1 loops=1)

Filter: ((handled_by IS NULL) AND (organization_id = 1612) AND
((status)::text <> ALL ('{sent_to_proxy,in_progress,ok}'::text[])))

Rows Removed by Filter: 600140

Planning time: 0.490 ms

Execution time: 232.246 ms

(6 rows)

# set enable_seqscan = off;

SET

# explain analyze SELECT 1 AS one FROM "external_sync_messages" WHERE
"external_sync_messages"."organization_id" = 1612 AND
("external_sync_messages"."status" NOT IN ('sent_to_proxy', 'in_progress',
'ok')) AND "external_sync_messages"."handled_by" IS NULL LIMIT 1;

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Limit (cost=0.42..39.88 rows=1 width=0) (actual time=0.030..0.030 rows=1
loops=1)

-> Index Scan using index_external_sync_messages_on_organization_id on
external_sync_messages (cost=0.42..251934.05 rows=6385 width=0) (actual
time=0.028..0.028 rows=1 loops=1)

Index Cond: (organization_id = 1612)

Filter: ((handled_by IS NULL) AND ((status)::text <> ALL
('{sent_to_proxy,in_progress,ok}'::text[])))

Planning time: 0.103 ms

Execution time: 0.052 ms

(6 rows)

# SELECT attname, inherited, n_distinct, array_to_string(most_common_vals,
E'\n') as most_common_vals FROM pg_stats WHERE tablename =
'external_sync_messages' and attname IN ('status', 'organization_id',
'handled_by');

attname | inherited | n_distinct | most_common_vals

-----------------+-----------+------------+------------------

handled_by | f | 3 | 3 +

| | | 236140 +

| | | 54413

organization_id | f | 22 | 1612 +

| | | 287 +

| | | 967 +

| | | 1223 +

| | | 1123 +

| | | 1930 +

| | | 841 +

| | | 1814 +

| | | 711 +

| | | 1513 +

| | | 1794 +

| | | 1246 +

| | | 1673 +

| | | 1552 +

| | | 1747 +

| | | 2611 +

| | | 2217 +

| | | 2448 +

| | | 2133 +

| | | 1861 +

| | | 2616 +

| | | 2796

status | f | 6 | ok +

| | | ignored +

| | | channel_error +

| | | in_progress +

| | | error +

| | | sent_to_proxy

(3 rows)

# select count(*) from external_sync_messages;

count

--------

992912

(1 row)