Postgresql – Postgres not using the index even when rows returned is 5% of the table

execution-planindexperformancepostgresqlquery-performance

I have a postgres table with the following structure:

+---------+-------------+-------------+----------+---------+---------+
|   id    |  timestamp  |   numvalues |  text1   |  text2  |  text3  |
+---------+-------------+-------------+----------+---------+---------+
|abcd12344|    4124135  |[1,2,53,1241]| apple    | banana  | papaya  |
+---------+-------------+-------------+----------+---------+---------+

id – random alphanumeric value
timestamp – epoch timestamp
numvalues – array of integers
text(n) – text values

The table has about 150 million rows.

I make an inner query to get the nth percentile of the data ordered on one of the values in the array. Then, I need to get the averages of several values from the array. The length of the array is around 31.

SELECT AVG(COALESCE(numvalues[2], 0))::NUMERIC(10,0), AVG(COALESCE(numvalues[3], 0))::NUMERIC(10,0)...AVG(COALESCE(numvalues[12], 0))::NUMERIC(10,0) 
FROM (SELECT timestamp, numvalues, ntile(100) 
      OVER (ORDER BY numvalues[1]) pval FROM tablename WHERE timestamp >= somevalue and timestamp <= somevalue) innertable 
WHERE pval >= x and pval <= y;

This returns about 7 million rows out of 150 million which is about 5% of the table. However, it does not use the index. Running an EXPLAIN ANALYZE shows that it uses a Seq Scan instead, even when enable_seqscan is set to off.

However, a similar query :

SELECT text1, count(distinct(id))
FROM (SELECT timestamp, id, text1, numvalues, ntile(100) 
      OVER (ORDER BY numvalues[1]) pval FROM tablename WHERE timestamp >= somevalue and timestamp <= somevalue) innertable 
WHERE pval >= x and pval <= y GROUP BY text1;

does use the index.

The index is on the timestamp column

The results of EXPLAIN ANALYZE:

explain analyze select text1, count(distinct(id)) 
    from (select timestamp, text1, numvalues, id, ntile(100) over (order by numvalues[1]) pval from table where timestamp >= 1431100800 and timestamp <= 1431108000 and numvalues[1] NOTNULL) innertable 
    where pval >= 90 and pval <= 90 group by text1;

                                  QUERY PLAN

--------------------------------------------------------------------------------
GroupAggregate  (cost=4554118.06..4554641.66 rows=1 width=28) (actual time=218641.221..219051.984 rows=20 loops=1)
   ->  Sort  (cost=4554118.06..4554292.59 rows=69812 width=28) (actual time=218640.546..218728.294 rows=71441 loops=1)
         Sort Key: innertable.text1
         Sort Method: quicksort  Memory: 8654kB
         ->  Subquery Scan on innertable  (cost=4094722.75..4548501.27 rows=69812 width=28) (actual time=216502.946..218521.666 rows=71441 loops=1)
           Filter: ((innertable.pval >= 90) AND (innertable.pval <= 90))
           Rows Removed by Filter: 7072674
           ->  WindowAgg  (cost=4094722.75..4339065.03 rows=13962416 width=118) (actual time=202276.333..211374.235 rows=7144115 loops=1)
                 ->  Sort  (cost=4094722.75..4129628.79 rows=13962416 width=118) (actual time=164912.487..190272.316 rows=7144115 loops=1)
                       Sort Key: (table.numvalues[9])
                       Sort Method: external merge  Disk: 1387704kB
                       ->  Index Scan using table_time_idx on table  (cost=0.57..1578710.87 rows=13962416 width=118) (actual time=0.124..141014.505 rows=7144115 loops=1)
                             Index Cond: (("timestamp" >= 1431100800) AND ("timestamp" <= 1431108000))
                             Filter: (numvalues[1] IS NOT NULL)
                             Rows Removed by Filter: 7090075
 Total runtime: 219340.709 ms
(16 rows)



explain analyze select avg(coalesce(numvalues[9], 0))::NUMERIC(10,0) mean9, avg(coalesce(numvalues[30],0))::NUMERIC(10,0),  avg(coalesce(numvalues[8],0))::NUMERIC(10,0) - avg(coalesce(numvalues[30], 0))::NUMERIC(10,0) mean0, avg(coalesce(numvalues[10],0))::NUMERIC(10,0) - avg(coalesce(numvalues[8], 0))::NUMERIC(10,0) mean1, avg(coalesce(numvalues[14],0))::NUMERIC(10,0) - avg(coalesce(numvalues[10], 0))::NUMERIC(10,0) mean2, avg(coalesce(numvalues[13],0))::NUMERIC(10,0) - avg(coalesce(numvalues[14], 0))::NUMERIC(10,0) mean3, avg(coalesce(numvalues[9],0))::NUMERIC(10,0) - avg(coalesce(numvalues[13], 0))::NUMERIC(10,0) mean4 
    from (select timestamp, id, numvalues, ntile(100) over (order by numvalues[1] ) pval from table where timestamp >= 1431093600.00 and timestamp <= 1431100800.00 and numvalues[9] NOTNULL) innerTable 
    where pval >= 90.00 and pval <= 90.00 ;

QUERY PLAN                                                                        
--------------------------------------------------------------------------------
 Aggregate  (cost=12662077.32..12662077.37 rows=1 width=82) (actual time=650343.769..650343.770 rows=1 loops=1)
   ->  Subquery Scan on innertable  (cost=12634854.54..12661968.84 rows=3615 width=82) (actual time=647745.962..650232.725 rows=71441 loops=1)
         Filter: (((innertable.pval)::numeric >= 90.00) AND ((innertable.pval)::numeric <= 90.00))
         Rows Removed by Filter: 7072674
          ->  WindowAgg  (cost=12634854.54..12647507.88 rows=723048 width=248) (actual time=632388.293..642338.237 rows=7144115 loops=1)
           ->  Sort  (cost=12634854.54..12636662.16 rows=723048 width=248) (actual time=599893.771..617413.102 rows=7144115 loops=1)
                 Sort Key: (table.numvalues[9])
                 Sort Method: external merge  Disk: 3214248kB
                 ->  Seq Scan on req_p0swajch2t  (cost=0.00..12480460.48 rows=723048 width=248) (actual time=0.041..575423.062 rows=7144115 loops=1)
                       Filter: ((numvalues[1] IS NOT NULL) AND (("timestamp")::numeric >= 1431100800.00) AND (("timestamp")::numeric <= 1431108000.00))
                       Rows Removed by Filter: 138191935
Total runtime: 650785.126 ms
(12 rows)

Can anyone help me out with why Postgres would use an index for one query and not the other? Running a VACUUM ANALYZE didn't help either.

Is there any way to speed up the queries? A query over the whole table would take about 20 – 30 minutes! Partitioning didn't make much difference since queries span multiple partitions and it showed an improvement of only about a couple of minutes when the number of partitions spanned became larger.

Best Answer

From your query plans, it looks like you're comparing ints to ints in the first query plan, and int to numeric in the second plan.

Your first compare:

Index Cond: (("timestamp" >= 1431100800) AND ("timestamp" <= 1431108000))

and

timestamp >= 1431100800 and timestamp <= 1431108000

In the second query, it's numeric values:

Filter: ((numvalues[1] IS NOT NULL) AND (("timestamp")::numeric >= 1431100800.00) AND (("timestamp")::numeric <= 1431108000.00))

and

timestamp >= 1431093600.00 and timestamp <= 1431100800.00

Casting to numeric causes the index to be ignored in favor of a sequential scan.

You can see this with a very simple example, set up below:

CREATE TABLE t2 (a int);
CREATE INDEX t2_a_idx ON t2(a);
INSERT INTO t2 (a) SELECT i FROM generate_series(1,1000000) AS i;
VACUUM ANALYZE VERBOSE t2;

My first query plan looks like this:

EXPLAIN ANALYZE SELECT * FROM t2 WHERE a > 750000;

Index Only Scan using t2_a_idx on t2 (cost=0.42..7134.65 rows=250413 width=4) 
(actual time=0.019..29.926 rows=250000 loops=1)
Index Cond: (a > 750000)
Heap Fetches: 0
Planning time: 0.137 ms
Execution time: 39.114 ms
(5 rows)
Time: 39.540 ms

While a second query using numerics looks like this:

EXPLAIN ANALYZE SELECT * FROM t2 WHERE a > 750000.00;

Seq Scan on t2  (cost=0.00..19425.00 rows=333333 width=4) (actual time=122.803..175.326 rows=250000 loops=1)
Filter: ((a)::numeric > 750000.00)
Rows Removed by Filter: 750000
Planning time: 0.058 ms
Execution time: 184.194 ms
(5 rows)
Time: 184.487 ms

In the second instance here, the index is ignored in favor of a sequential scan because of the cast to a numeric value, which looks like exactly what's happening in your two examples.

One last aside, you might be able to speed your query up via a SET query before executing it as well:

SET work_mem = 2GB;

If your server can handle it, because your sorts are spilling to disk, as noted in this line from your query plan:

Sort Method: external merge  Disk: 1387704kB

Hope this helps. =)

Related Question