Postgres: Sorting by an immutable function index doesn't use index

Question

I have a simple table.

CREATE TABLE posts
(
    id uuid NOT NULL,
    vote_up_count integer,
    vote_down_count integer,
    CONSTRAINT post_pkey PRIMARY KEY(id)
);

I have an IMMUTABLE function that does simple (but could be complex) arithmetic.

CREATE OR REPLACE FUNCTION score(
    ups integer,
    downs integer)
  RETURNS integer AS
$BODY$
    select $1 - $2
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;
ALTER FUNCTION score(integer, integer)
  OWNER TO postgres;

I create an index on the posts table that uses my function.

CREATE INDEX posts_score_index ON posts(score(vote_up_count, vote_down_count), date_created);

When I EXPLAIN the following query, it doesn't seem to be using the index.

SELECT * FROM posts ORDER BY score(vote_up_count, vote_down_count), date_created

Sort  (cost=1.02..1.03 rows=1 width=310)
  Output: id, date_created, last_edit_date, slug, sub_id, user_id, user_ip, type, title, content, url, domain, send_replies, vote_up_count, vote_down_count, verdict, approved_by, removed_by, verdict_message, number_of_reports, ignore_reports, number_of_com (...)"
  Sort Key: ((posts.vote_up_count - posts.vote_down_count)), posts.date_created
  ->  Seq Scan on public.posts  (cost=0.00..1.01 rows=1 width=310)
        Output: id, date_created, last_edit_date, slug, sub_id, user_id, user_ip, type, title, content, url, domain, send_replies, vote_up_count, vote_down_count, verdict, approved_by, removed_by, verdict_message, number_of_reports, ignore_reports, number_ (...)

How do I get my ORDER BY to use an index from an IMMUTABLE function that could have some very complex arithmetic?

EDIT: Based off of @Егор-Рогов's suggestions, I change the query a bit to see if I can get it to use an index. Still no luck.

set enable_seqscan=off;
EXPLAIN VERBOSE select date_created from posts ORDER BY (hot(vote_up_count, vote_down_count, date_created),date_created);

Here is the output.

Sort  (cost=10000000001.06..10000000001.06 rows=1 width=16)
  Output: date_created, (ROW(round((((log((GREATEST(abs((vote_up_count - vote_down_count)), 1))::double precision) * sign(((vote_up_count - vote_down_count))::double precision)) + ((date_part('epoch'::text, date_created) - 1134028003::double precision) / 4 (...)
  Sort Key: (ROW(round((((log((GREATEST(abs((posts.vote_up_count - posts.vote_down_count)), 1))::double precision) * sign(((posts.vote_up_count - posts.vote_down_count))::double precision)) + ((date_part('epoch'::text, posts.date_created) - 1134028003::dou (...)
  ->  Seq Scan on public.posts  (cost=10000000000.00..10000000001.05 rows=1 width=16)
        Output: date_created, ROW(round((((log((GREATEST(abs((vote_up_count - vote_down_count)), 1))::double precision) * sign(((vote_up_count - vote_down_count))::double precision)) + ((date_part('epoch'::text, date_created) - 1134028003::double precision (...)

EDIT2: It seems that I was not using the index because of a second order by with date_created.

Well, you are reading all rows from the table. An index is primarily there to reduce the number of rows. It's seldomly used to support sorting. If at all you'd need a covering index that includes all columns you are selecting. — user330315
– user330315, Commented Oct 25, 2015 at 11:12

Egor Rogov · Accepted Answer · 2015-10-25 11:03:56Z

1

I can see a couple of points that discourages the planner from using the index.

1. Look at this line in the explain output:

Seq Scan on public.posts  (cost=0.00..1.01 rows=1 width=310)

It says that the planner believes there is only one row in the table. In this case it makes no sense to use index scan, for sequential scan is faster.

Try to add more rows to the table, do analyze and try again. You can also test it by temporarily disabling sequential scans by set enable_seqscan=off;.

2. You use your the function to sort the results. So the planner may decide to use the index in order to get tuple ids in the correct order. But then it needs to fetch each tuple from the table to get values of all columns (because of select *).

You can make the index more attractive to the planner by adding all necessary columns to it, which make possible to avoid table scan. This is called index-only scan.

CREATE INDEX posts_score_index ON posts(
  score(vote_up_count, vote_down_count),
  date_created,
  id,             -- do you actually need it in result set?
  vote_up_count,  -- do you actually need it in result set?
  vote_down_count -- do you actually need it in result set?
);

And make sure you run vacuum after inserting/updating/deleting rows to update the visibility map.

The downside is the increased index size, of course.

answered Oct 25, 2015 at 11:03

Egor Rogov

5,48828 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Paul Knopf Over a year ago

I just updated my question. The query

set enable_seqscan=off; EXPLAIN VERBOSE select date_created from posts ORDER BY (hot(vote_up_count, vote_down_count, date_created),date_created);

still did not use the index.

Egor Rogov Over a year ago

What is hot() here? Please provide all the scripts to reproduce the issue. Your original query uses the index, I tested that.

Paul Knopf Over a year ago

I'm sorry. I have multiple functions that use an index. I'm mixing them up. It is all arithmetic.

Paul Knopf Over a year ago

UPDATE: It seems my index was not being used because my query had a second order by clause. I removed the second date_created order by and it is now using the index.

Collectives™ on Stack Overflow

Postgres: Sorting by an immutable function index doesn't use index

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related