1

I have two identical databases, development and live.

I run this query on both of them....

select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)

On the dev box the query takes about 100ms, on live it takes about an hour.

Both the tables have a primary-key field called deviceId, both databases have identical structure, and both have very similar data (non of the tables contain more than million rows). Both databases are vacuumed/analysed regularly, both have been done within the last 12 hours.

Each database is on a different computer, the hardware is different for each, but for 99% of the time the live box is about 10% slower.

As far as I can tell, both databases are configured the same.

My guess is that there's some subtle difference in the distribution of the data on each database which effects the choice of the explain plan.

My questions are:

  • Is there anyway to force the database to use a particular index?
  • Is there a different form of the SQL query that would produce the same results but quicker?

explain plan dev - takes less than a second:

Aggregate  (cost=7806.68..7806.69 rows=1 width=8)
  ->  Seq Scan on deviceb  (cost=4624.86..7702.06 rows=41848 width=0)
        Filter: (NOT (hashed SubPlan 1))
        SubPlan 1
          ->  Unique  (cost=0.29..4404.59 rows=88108 width=7)
                ->  Index Only Scan using device_pkey on device  (cost=0.29..4184.32 rows=88108 width=7)

explain plan live - takes about an hour:

Finalize Aggregate  (cost=747368287.67..747368287.68 rows=1 width=8)
  ->  Gather  (cost=747368287.46..747368287.67 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=747367287.46..747367287.47 rows=1 width=8)
              ->  Parallel Seq Scan on deviceb  (cost=0.42..747367217.98 rows=27789 width=0)
                    Filter: (NOT (SubPlan 1))
                    SubPlan 1
                      ->  Materialize  (cost=0.42..26001.96 rows=156913 width=7)
                            ->  Unique  (cost=0.42..22822.40 rows=156913 width=7)
                                  ->  Index Only Scan using device_pkey on device  (cost=0.42..21930.12 rows=156913 width=7)

I've compared the dev/live versions of the tables using pgAdmin4 - simply by highlighting the object and selecting the properties of each table and it's indexes and constraints.

Also - my tests (on both databases) were done with no other users connected.

DDL, as requested:

CREATE TABLE public.device (
    deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
    recentfixtime timestamp without time zone,
    newfixes integer DEFAULT 0,
    lastfixid bigint DEFAULT 0,
    CONSTRAINT device_pkey PRIMARY KEY (deviceid)
        USING INDEX TABLESPACE fastspace
);


CREATE TABLE public.deviceb  (
    deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
    flightid integer DEFAULT 0,
    lastfirstfixtime timestamp without time zone,
    lastprocfixtime timestamp without time zone,
    lastprocfly boolean,
    priority integer DEFAULT 0,
    lastlandtime timestamp without time zone,
    CONSTRAINT deviceb_pkey PRIMARY KEY (deviceid)
        USING INDEX TABLESPACE fastspace
)

UPDATE - SOLVED, thanks to Jjanes, suggested version is blisteringly fast on both databases.

select count(*) FROM DeviceB where NOT EXISTS (
    SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)

I'll admit I'm still not sure what the root cause is, but for completeness I'll try to answer some of the further questions. Autoanalyse is still on (pgAdmin/statistics shows a last Autoanalyse date earlier this morning. both tables (on live) have less than 200,000 rows, both tables on dev have less than 100,000. The hardware on the dev/live boxes is different (biggest difference is that dev has 32G memory, live only 16G). Possibly the most significant difference is in versions, dev is 13.1, live is still on 10.4

5
  • Maybe we can extract more info from the execution plans than you can. But only if we can see them....
    – jjanes
    Nov 6, 2021 at 15:51
  • Share the plans, how are you making sure the structure is identical? My opinion is that statistics from production should be copied to dev to ensure your production plans are used in dev. My stab in the dark guess is that the primary key column is not really a primary key and can actually be null, this will cause the not in to behave very differently. Note that for not in (select distinct , the distinct is superfluous. Nov 6, 2021 at 16:07
  • Please provide the DDL for creating your two tables - device and deviceb!
    – Vérace
    Nov 6, 2021 at 18:45
  • Please start any such question by disclosing the Postgres version(s) in use. Nov 7, 2021 at 0:27
  • Using just the SubPlan rather than the hashed SubPlan is an utter disaster. What is the work_mem setting of both servers? What is the full Postgresql version of both? Why is the estimated row count for the subquery for one of them twice the other?
    – jjanes
    Nov 7, 2021 at 2:19

2 Answers 2

1

I've asked a series of questions probing for the root cause. But regardless of the exact details of the root cause, you can insulate yourself from the problem by using NOT EXISTS rather than NOT IN.

select count(*) FROM DeviceB where NOT EXISTS (
    SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)
0

Is there anyway to force the database to use a particular index?

No. Not directly. You can "disable" other access methods to make Postgres chose an index scan or bitmap index scan. See:

But you cannot directly instruct it to use a certain index. That's not the problem here anyway.

Is there a different form of the SQL query that would produce the same results but quicker?

Yes. Drop DISTINCT:

SELECT count(*) FROM deviceb
WHERE deviceid NOT IN (SELECT deviceid FROM device);

Trying to remove duplicates from big sets is expensive. Since device.deviceid is the PRIMARY KEY, there cannot be duplicates.

If NULL values or duplicates could be involved, we'd look at other query styles, but for two primary keys, the query should be the optimum.

The parallel sequential scan on deviceb is the problem, as we see here:

Parallel Seq Scan on deviceb (cost=0.42..747367217.98 rows=27789 width=0)

No index involved. That shouldn't be nearly as expensive. I also wonder why we don't see a line like:

'Rows Removed by Filter: 123456'

What version of Postgres is that? And how did you produce the query plan exactly?

Either something is wrong with the table:

  • Many more rows than you think? Check with:
SELECT count(*) FROM deviceb;
  • Extreme table and index bloat? Check with:
SELECT pg_size_pretty(pg_table_size('deviceb'))
     , pg_size_pretty(pg_table_size('deviceb_pkey'));

SELECT * FROM pg_stat_user_tables WHERE relname = 'deviceb';

Fix with another VACUUM ANALYZE deviceb;, maybe even VACUUM FULL ANALYZE deviceb; since there are no other users connected anyway (?)

  • Exclusive locks?

That can't be it. You said:

no other users connected.

  • Hardware problems?

Or something is wrong with parallelism. Try disabling it (for debugging!) and retest:

SET max_parallel_workers_per_gather = 0;

Also:

Both databases are vacuumed/analysed regularly, both have been done within the last 12 hours.

That sounds like you are running manual VACUUM / ANALYZE. Did you disable autovacuum? (Typically, you shouldn't.)

While the painfully slow (parallel) sequential scan must be looked into, I wonder why we don't see an index-only scans on diviceb to begin with. With freshly VACUUM'ed tables and a TABLESPACE named fastspace I'd expect as much. Are you aware that some key parameters can be set per TABLESPACE? Most importantly seq_page_cost and random_page_cost. Like:

ALTER TABLESPACE fastspace SET (seq_page_cost = 0.5, random_page_cost = 1);

Adjust to the characteristics of your "fastspace".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.