为何仅单个索引生效？timedevent表多索引查询优化疑问

Hey there, let's dig into why only your eventdate index is being used when running your query. First, let's recap your setup to make sure we're on the same page:

Your table definition:

CREATE TABLE timedevent ( 
  id bigint NOT NULL, 
  eventdate timestamp with time zone NOT NULL, 
  newstateids character varying(255) NOT NULL, 
  sourceid character varying(255) NOT NULL, 
  CONSTRAINT "timedevent_pkey" PRIMARY KEY (id) 
) WITH (OIDS=FALSE);

Your indexes:

• B-tree on eventdate and newstateids
• Hash index on sourceid

Your query goal: Fetch rows between two dates, matching a specific newstateids value, where sourceid is in a specified set.

Why Your Indexes Aren't Being Used #

Let's break down the issues one by one:

1. Hash indexes don't play nice with IN queries
   PostgreSQL's hash indexes only support exact equality checks (=), not IN clauses (which act like multiple equality checks). The query planner will almost always ignore a hash index for IN operations because B-tree indexes are far better suited for this scenario. Hash indexes have extremely narrow use cases in PostgreSQL—stick with B-tree for most string/number equality or range needs.

2. newstateids index might not match your query pattern
   If your query uses partial matching (like newstateids LIKE '%some-value%') instead of an exact = match, your B-tree index won't help. B-tree indexes only work efficiently for prefix matches (e.g., LIKE 'some-value%') or exact matches. If you're storing multiple state IDs in that string (like comma-separated values), a B-tree index is the wrong tool entirely—you'd be better off using an array type with a GIN index, or splitting values into a separate junction table.

3. Single-column indexes might not combine efficiently
   When filtering on multiple columns at once, PostgreSQL can struggle to combine single-column indexes effectively. A composite index tailored to your exact query is often more likely to be picked up by the planner.

Fixes to Get Indexes Working as Expected #

Here's what you can try:

• Replace the hash index on sourceid with a B-tree index
  Drop the hash index and create a B-tree one instead—this will make the IN clause use the index properly:

  DROP INDEX IF EXISTS sourceid_hash_idx;
  CREATE INDEX sourceid_btree_idx ON timedevent USING btree (sourceid);
  

• Adjust the newstateids index based on your query

  • If you're doing exact matches: Run ANALYZE timedevent; to refresh table statistics—outdated stats can make the planner skip indexes even if they're useful.
  • If you're storing multiple values or doing partial matches: Switch to an array type for better indexing. For example:

    -- Convert comma-separated string to integer array
    ALTER TABLE timedevent ALTER COLUMN newstateids TYPE integer[] USING string_to_array(newstateids, ',')::integer[];
    -- Create GIN index for array containment checks
    CREATE INDEX newstateids_gin_idx ON timedevent USING gin (newstateids);
    

    Then query with newstateids @> ARRAY[123] to check if the array contains your target state ID.

• Create a composite index for your specific query
  If you frequently run this exact combination of filters, a composite index can be even more efficient. Order matters here—start with columns used for range conditions (like eventdate) or the column with the highest selectivity (most unique values):

  CREATE INDEX timedevent_query_idx ON timedevent USING btree (eventdate, newstateids, sourceid);
  

• Check the execution plan
  Always run EXPLAIN ANALYZE on your query to see exactly what the planner is doing. For example:

  EXPLAIN ANALYZE
  SELECT * FROM timedevent
  WHERE eventdate BETWEEN '2023-01-01' AND '2023-12-31'
    AND newstateids = 'target-state'
    AND sourceid IN ('source1', 'source2', 'source3');
  

  This will show you if indexes are being used, or if the planner is choosing a sequential scan (which might be intentional for large result sets, but not for small filtered subsets).

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