Indexing is not applying on the date column in postgres. Top-N-heapsort is applying not Index scan

Question

I have a table 'post' which has millions of rows. I need top N records using the date field but it's taking too much time because of Top-N-heapsort. How can I optimize this query?

Note: On the staging server where I have less data it's working fine. Index scan is applying there.

DB Version: Postgres 12

    CREATE TABLE public.posts
(
    post_id integer NOT NULL DEFAULT (primary key),
    team_id integer NOT NULL,
    is_active boolean DEFAULT true,
    "createdAt" timestamp with time zone,
    "updatedAt" timestamp with time zone,
)

Index column are:

"createdAt DESC NULLS Last",
(team_id ASC NULLS LAST, "createdAt" DESC NULLS LAST),
team_id ASC NULLS LAST

QUERY:

SELECT p.*
FROM posts p
WHERE team_id = 1
AND p.is_active = true AND "createdAt" IS NOT NULL
ORDER BY "createdAt" DESC 
LIMIT 20;

Query plan:

 "Limit  (cost=138958.67..138958.72 rows=20 width=360) (actual time=356.391..356.419 rows=20 loops=1)"
"  ->  Sort  (cost=138958.67..139078.57 rows=47960 width=360) (actual time=356.389..356.402 rows=20 loops=1)"
"        Sort Key: ""createdAt"" DESC"
"        Sort Method: top-N heapsort  Memory: 34kB"
"        ->  Index Scan using posts_team_id on posts p  (cost=0.44..137682.47 rows=47960 width=360) (actual time=0.042..317.258 rows=52858 loops=1)"
"              Index Cond: (team_id = 1)"
"              Filter: (is_active AND (""createdAt"" IS NOT NULL))"
"Planning Time: 0.145 ms"
"Execution Time: 356.459 ms"

score 1 · Accepted Answer · answered Mar 09 '21 at 03:55

1

This is the index I would use:

CREATE INDEX ON posts (team_id, "createdAt") WHERE is_active;

It supports the WHERE condition and the ORDER BY.

answered Mar 09 '21 at 03:55

Laurenz Albe

209,280
17
206
263

will give it a try tonight but inactive post count is very less. it's in hundreds – Nishant Khandelwal Mar 09 '21 at 07:26
Then you can omit the `WHERE` condition. – Laurenz Albe Mar 09 '21 at 07:29
I already have this index as I mentioned in the question. (team_id ASC NULLS LAST, "createdAt" DESC NULLS LAST) – Nishant Khandelwal Mar 09 '21 at 07:39
No, you don't, because you specified `NULLS LAST` both times. That makes a difference. – Laurenz Albe Mar 09 '21 at 07:41
Ok.. will try this – Nishant Khandelwal Mar 09 '21 at 07:43

score 0 · Answer 2 · answered Mar 08 '21 at 20:27

0

For this query:

SELECT p.*
FROM posts p
WHERE team_id = 1 AND
      p.is_active = true AND
      "createdAt" IS NOT NULL
ORDER BY "createdAt" DESC 
LIMIT 20;

I would suggest writing this without the is not null and creating an index on (team_id, is_active, createdAt desc).

answered Mar 08 '21 at 20:27

Gordon Linoff

1,242,037
58
646
786

not working... its taking top-N-heapsort only – Nishant Khandelwal Mar 08 '21 at 20:43

score 0 · Answer 3 · answered Mar 09 '21 at 01:04

It won't use a DESC NULLS LAST index to support an ORDER BY...DESC NULLS FIRST query. (NULLS FIRST is the default for DESC when nothing is specified, so that is what your query is doing).

It seems rather weird that you would go out of your way to specify an ordering in the index, but not make it match your query.

Either your stage server has a different index, or is running a different query.

Indexing is not applying on the date column in postgres. Top-N-heapsort is applying not Index scan

3 Answers3