Skip to main content
added 34 characters in body
Source Link
Laurenz Albe
  • 56.5k
  • 4
  • 50
  • 82

First, try to gather accurate statistics:

ANALYZE a;

If that doesn't help, you can choose the ugly solution of rewriting the query so that you force a nested loop join:

SELECT count(*)
FROM a
CROSS JOIN LATERAL (SELECT FROM b
                    WHERE a.id = b.a_id
                    OFFSET 0)
WHERE a.num1 IS NOT NULL
  AND a.num2 IS NOT NULL;

First, try to gather accurate statistics:

ANALYZE a;

If that doesn't help, you can choose the ugly solution of rewriting the query so that you force a nested loop join:

SELECT count(*)
FROM a
CROSS JOIN LATERAL (SELECT FROM b
                    WHERE a.id = b.a_id)
WHERE a.num1 IS NOT NULL
  AND a.num2 IS NOT NULL;

First, try to gather accurate statistics:

ANALYZE a;

If that doesn't help, you can choose the ugly solution of rewriting the query so that you force a nested loop join:

SELECT count(*)
FROM a
CROSS JOIN LATERAL (SELECT FROM b
                    WHERE a.id = b.a_id
                    OFFSET 0)
WHERE a.num1 IS NOT NULL
  AND a.num2 IS NOT NULL;
deleted 181 characters in body
Source Link
Laurenz Albe
  • 56.5k
  • 4
  • 50
  • 82

I guess that the following index should do the trickFirst, try to gather accurate statistics:

CREATE INDEX ON a (id) WHERE num1 IS NOT NULL AND num2 IS NOT NULL;

VACUUM (ANALYZE) a;

The ANALYZE might improve the horrible estimate, and even withoutIf that an index-only scan should makedoesn't help, you can choose the fast plan more attractive.

A less elegantugly solution is to force the hand of rewriting the optimizerquery so that you force a nested loop join:

SELECT count(*)
FROM a
CROSS JOIN LATERAL (SELECT FROM b
                    WHERE a.id = b.a_id)
WHERE a.num1 IS NOT NULL
  AND a.num2 IS NOT NULL;

I guess that the following index should do the trick:

CREATE INDEX ON a (id) WHERE num1 IS NOT NULL AND num2 IS NOT NULL;

VACUUM (ANALYZE) a;

The ANALYZE might improve the horrible estimate, and even without that an index-only scan should make the fast plan more attractive.

A less elegant solution is to force the hand of the optimizer:

SELECT count(*)
FROM a
CROSS JOIN LATERAL (SELECT FROM b
                    WHERE a.id = b.a_id)
WHERE a.num1 IS NOT NULL
  AND a.num2 IS NOT NULL;

First, try to gather accurate statistics:

ANALYZE a;

If that doesn't help, you can choose the ugly solution of rewriting the query so that you force a nested loop join:

SELECT count(*)
FROM a
CROSS JOIN LATERAL (SELECT FROM b
                    WHERE a.id = b.a_id)
WHERE a.num1 IS NOT NULL
  AND a.num2 IS NOT NULL;
Source Link
Laurenz Albe
  • 56.5k
  • 4
  • 50
  • 82

I guess that the following index should do the trick:

CREATE INDEX ON a (id) WHERE num1 IS NOT NULL AND num2 IS NOT NULL;

VACUUM (ANALYZE) a;

The ANALYZE might improve the horrible estimate, and even without that an index-only scan should make the fast plan more attractive.

A less elegant solution is to force the hand of the optimizer:

SELECT count(*)
FROM a
CROSS JOIN LATERAL (SELECT FROM b
                    WHERE a.id = b.a_id)
WHERE a.num1 IS NOT NULL
  AND a.num2 IS NOT NULL;