CODEWITHSUNDEEP
postgresqlindexingsql-order-by
Paul Knopf
Paul Knopf

Reputation: 9776

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

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.

Upvotes: 0

Views: 164

Answers (1)

Egor Rogov
Egor Rogov

Reputation: 5398

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.

Upvotes: 1

Related Questions