带WHERE条件的PostgreSQL快速计数方案及Sequelize实现咨询

Hey there! Let’s figure out how to speed up that conditional count query in PostgreSQL—since you can tolerate some inaccuracy, we’ve got a few solid options. Plus, I’ll cover how to implement each in Sequelize.

First, let’s recap your slow query and its execution plan to understand the root issue:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) AS "count" FROM "Orders" AS "Order" WHERE "Order"."orderType" = 'online' AND "Order"."status" != 'paid';
返回结果：
Aggregate (cost=47268.10..47268.11 rows=1 width=8) (actual time=719.722..719.723 rows=1 loops=1)
Buffers: shared hit=32043
-> Seq Scan on "Orders" "Order" (cost=0.00..47044.35 rows=89501 width=0) (actual time=0.011..674.316 rows=194239 loops=1)
Filter: (((status)::text <> 'paid'::text) AND (("orderType")::text = 'online'::text))
Rows Removed by Filter: 830133
Buffers: shared hit=32043
Planning time: 0.069 ms
Execution time: 719.755 ms

The problem here is the full sequential scan—PostgreSQL has to read every row in the table, then filter out 80% of them. Let’s fix that.

1. Approximate Count via PostgreSQL Statistics (Super Fast) #

PostgreSQL maintains built-in statistics about your tables and columns that you can use for quick estimates. Error margins are usually 5-10% (depending on how fresh your stats are), which fits your tolerance for inaccuracy.

Query: #

SELECT 
  ROUND(
    n_live_tup 
    * (SELECT freq FROM pg_stats WHERE tablename = 'Orders' AND attname = 'orderType' AND val = 'online')
    * (SELECT freq FROM pg_stats WHERE tablename = 'Orders' AND attname = 'status' AND val = 'paid')
  ) AS estimated_count
FROM pg_stat_user_tables 
WHERE relname = 'Orders';

Notes: #

• Refresh stats periodically with ANALYZE Orders; if your data changes often (this is lightweight, way faster than a full count).
• This works by multiplying total live rows by the frequency of each condition’s value in column stats.

2. Partial Index + Exact Fast Count (Best for Repeated Queries) #

If you run this specific conditional count often, create a partial index that only includes rows matching your WHERE clause. This turns the count into an index scan instead of a full table scan—giving exact results at a fraction of the time.

Step 1: Create the Partial Index #

CREATE INDEX idx_orders_online_paid ON Orders (id) 
WHERE orderType = 'online' AND status = 'paid';

(We use id here because it’s a non-null, indexed column—any non-null column works for count(*))

Step 2: Run Your Original Query #

PostgreSQL will automatically use the index, and your execution time should drop to single-digit milliseconds:

SELECT count(*) AS "count" FROM "Orders" AS "Order" 
WHERE "Order"."orderType" = 'online' AND "Order"."status" = 'paid';

3. Sampling with TABLESAMPLE (Controllable Accuracy/Speed) #

Use TABLESAMPLE to scan a random subset of your table, then extrapolate the count. Adjust the sample percentage to balance speed and accuracy.

Query (10% Sample): #

SELECT 
  (count(*) * 10) AS estimated_count
FROM "Orders" AS "Order" 
WHERE "Order"."orderType" = 'online' AND "Order"."status" = 'paid'
TABLESAMPLE SYSTEM (10); -- SYSTEM = fast block-based sampling; use BERNOULLI for row-based (slower, more random)

• Increase the percentage (e.g., SYSTEM (20)) for more accuracy, decrease for faster results.

1. Approximate Count via Statistics #

Use Sequelize’s raw query method to run the stats-based estimate:

const { Sequelize, QueryTypes } = require('sequelize');
const sequelize = new Sequelize(/* your connection config */);

const getEstimatedCount = async () => {
  const result = await sequelize.query(`
    SELECT 
      ROUND(
        n_live_tup 
        * (SELECT freq FROM pg_stats WHERE tablename = 'Orders' AND attname = 'orderType' AND val = 'online')
        * (SELECT freq FROM pg_stats WHERE tablename = 'Orders' AND attname = 'status' AND val = 'paid')
      ) AS estimated_count
    FROM pg_stat_user_tables 
    WHERE relname = 'Orders';
  `, { type: QueryTypes.SELECT });
  
  return result[0].estimated_count;
};

// Usage
getEstimatedCount().then(count => console.log('Estimated count:', count));

2. Partial Index + Exact Count #

First, define the partial index in your Sequelize model:

// models/Order.js
module.exports = (sequelize, DataTypes) => {
  const Order = sequelize.define('Order', {
    orderType: DataTypes.STRING,
    status: DataTypes.STRING,
    // other fields...
  }, {
    indexes: [
      {
        name: 'idx_orders_online_paid',
        fields: ['id'],
        where: {
          orderType: 'online',
          status: 'paid'
        }
      }
    ]
  });
  return Order;
};

Then run the count query as usual—Sequelize will generate the correct SQL, and PostgreSQL will use the partial index:

const getExactFastCount = async () => {
  const count = await Order.count({
    where: {
      orderType: 'online',
      status: 'paid'
    }
  });
  return count;
};

// Usage
getExactFastCount().then(count => console.log('Exact fast count:', count));

3. Sampling with TABLESAMPLE #

Use a raw query in Sequelize to implement the sampling method:

const getSampledCount = async () => {
  const result = await sequelize.query(`
    SELECT (count(*) * 10) AS estimated_count
    FROM "Orders" AS "Order" 
    WHERE "Order"."orderType" = 'online' AND "Order"."status" = 'paid'
    TABLESAMPLE SYSTEM (10);
  `, { type: QueryTypes.SELECT });
  
  return result[0].estimated_count;
};

// Usage
getSampledCount().then(count => console.log('Sampled estimated count:', count));

内容的提问来源于stack exchange，提问作者farhan