I have a query that joins two tables but refuses to use use index unless I add a limit. The case is a bit complicated so best described by showing you.

I have two tables, users and user_latest_locations, that looks something like this except that the users table contain around 30 columns for different things.

    id serial PRIMARY KEY,
    name character varying(50) NOT NULL,
    description character varying(1000) NOT NULL,
    gender gender NOT NULL,
    looking_for_gender gender NOT NULL,
    latest_location geometry,
    search_radius integer NOT NULL,
    search_min_age integer NOT NULL,
    search_max_age integer NOT NULL,
    email character varying(50),
    password character varying(60) NOT NULL,
    birthdate date NOT NULL,
    last_activity timestamp without time zone NOT NULL,
    updated_time timestamp without time zone NOT NULL,
    created_time timestamp without time zone NOT NULL,
    status status NOT NULL DEFAULT 'pending'::status,
    country_code integer,
    mobile_number character varying(32),
    hide_contacts boolean NOT NULL DEFAULT true,
    occupation character varying(100) NOT NULL DEFAULT ''::character varying,
    school character varying(100) NOT NULL DEFAULT ''::character varying,
    hometown character varying(100) NOT NULL DEFAULT ''::character varying,
    hangouts character varying(1000) NOT NULL DEFAULT ''::character varying,
    popularity double precision NOT NULL DEFAULT 0,
    job character varying(100) NOT NULL DEFAULT ''::character varying,
    preview_push_message boolean NOT NULL DEFAULT true,
    region_ids integer[],
    intent intent NOT NULL DEFAULT 'fate'::intent,
    hide_mutual_contacts boolean NOT NULL DEFAULT false

CREATE UNIQUE INDEX users_mobile_idx ON users (mobile_number, country_code);

CREATE TABLE user_latest_locations (
  user_id integer NOT NULL PRIMARY KEY,
  last_activity timestamp without time zone NOT NULL,
  latest_location geometry

Both tables contain one row for every user, around 1 million in total.
The query looks like this:

SELECT * FROM users u
  LEFT JOIN user_latest_locations ull 
  ON ull.user_id = u.id
WHERE mobile_number IN (
  SELECT unnest('{1}'::text[]) as number 
  SELECT unnest('{1}'::text[]) as number)

And the resulting query plan looks like this:

Hash Right Join  (cost=24084.22..82388.58 rows=453489 width=306) (actual time=153.343..1123.307 rows=10 loops=1)
  Hash Cond: (ull.user_id = u.id)
  ->  Seq Scan on user_latest_locations ull  (cost=0.00..17549.25 rows=950725 width=44) (actual time=0.010..471.724 rows=949813 loops=1)
  ->  Hash  (cost=2472.61..2472.61 rows=453489 width=262) (actual time=0.527..0.527 rows=10 loops=1)
        Buckets: 16384  Batches: 64  Memory Usage: 129kB
        ->  Nested Loop  (cost=3.94..2472.61 rows=453489 width=262) (actual time=0.111..0.218 rows=10 loops=1)
              ->  HashAggregate  (cost=3.51..5.51 rows=200 width=0) (actual time=0.057..0.059 rows=1 loops=1)
                    Group Key: (unnest('{1}'::text[]))
                    ->  Append  (cost=0.00..3.01 rows=200 width=0) (actual time=0.023..0.040 rows=2 loops=1)
                          ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.019..0.022 rows=1 loops=1)
                          ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.004..0.006 rows=1 loops=1)
              ->  Index Scan using users_mobile_idx on users u  (cost=0.42..12.31 rows=2 width=262) (actual time=0.042..0.118 rows=10 loops=1)
                    Index Cond: ((mobile_number)::text = (unnest('{1}'::text[])))
Planning time: 1.310 ms
Execution time: 1123.851 ms

Why does the query do Seq Scan on user_latest_locations? Using its primary key would be much faster? If I add a LIMIT like below to the union it start using the index as expected.

SELECT * FROM users u
  LEFT JOIN user_latest_locations ull 
  ON ull.user_id = u.id
WHERE mobile_number IN (
  SELECT unnest('{1}'::text[]) as number 
  SELECT unnest('{1}'::text[]) as number limit 100)

The problem is that this is a function that take a array of mobile numbers which can be of varying length, it would be quite ugly to have a arbitrary limit inside the function.

I should also add that the stats for this table should indicate that its mostly unique values:

 n_distinct | correlation 
         -1 |   0.0183006

The behaviour is the same on both PostgreSQL 9.3 and 9.5. Analyzing the tables doesn't help.

  • What language are you writing this function in? – jjanes Mar 9 '16 at 21:32
  • I use plpgsql for the functions. – viblo Mar 10 '16 at 2:11
  • country_code in your unique index does not show up in the table. Please show your actual table definition. And always your version of Postgres. Did you recently run ANALYZE? And the values of which column are "mostly unique"? – Erwin Brandstetter Mar 10 '16 at 4:14
  • Are you sure you need all columns in the query? sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/… – dezso Mar 10 '16 at 9:46
  • yes, in the end this query return a user object. However, I can select out the user ids first and then do a second select if it would help performance wise. – viblo Mar 10 '16 at 9:57

The planner doesn't look inside your unnest operations and see that there is only one 1 value in the arrays. It makes its standard assumption that unknown arrays will have 100 elements each, or 200 for the union. So it thinks there will be a lot (453489) of users which meet the criteria, and that it will be faster to read the entire user_latest_locations and a hash join, rather than doing 453489 individual index lookups.

One possible solution is to do the unnest...union...unnest query separately and store the results into an array variable, and then pass that array to the main query. This way the planner is more likely to see the true size of the array.

  • I tried doing the unnest-union-unnest in a CTE to make sure it was retrieved first, but that didnt help. But if I extract it into a separate query to save into an array it will become better you say? I should also say that this query behaves in the same way in our production system with about 15 million rows. – viblo Mar 10 '16 at 2:11
  • Oh, why does it think it will find 453489 rows even if the array is 200 elements? According to the column stats this function will have all distinct values with very low correlation and no NULLs. – viblo Mar 10 '16 at 2:31
  • I wouldn't expect a CTE to help. The entire statement including CTE is still planned up front before any of it is executed. You need to get one part execute to completion and materialize the results, and then plan the other part. – jjanes Mar 10 '16 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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