Thread: Query not using Index

Query not using Index

From
Wei Shan
Date:
Hi all,

Please provide some advise on the following query not using the index:

pgsql version: 9.2.4
OS version: RedHat 6.5
Ram: 64 GB
rows in testdb: 180 million
shared_buffers: 16GB
effective_cache_size: 32GB
work_mem='32MB'

I have executed the query below after I vaccum analyze the table.

I have 2 questions:
  1. Why does the optimizer chose not to use the index when it will run faster?
  2. How do I ensure the optimizer will use the index without setting enable_seqscan='off'

Table structure.
testdb=# \d testtable
     Table "public.testtable"
      Column       |  Type   | Modifiers
-------------------+---------+-----------
 pk                | text    | not null
 additionaldetails | text    |
 authtoken         | text    | not null
 customid          | text    |
 eventstatus       | text    | not null
 eventtype         | text    | not null
 module            | text    | not null
 nodeid            | text    | not null
 rowprotection     | text    |
 rowversion        | integer | not null
 searchdetail1     | text    |
 searchdetail2     | text    |
 sequencenumber    | bigint  | not null
 service           | text    | not null
 timestamp         | bigint  | not null
Indexes:
    "testtable_pkey" PRIMARY KEY, btree (pk)
    "testtable_nodeid_eleanor1_idx" btree (nodeid) WHERE nodeid = 'eleanor1'::text, tablespace "tablespace_index"
    "testtable_nodeid_eleanor2_idx" btree (nodeid) WHERE nodeid = 'eleanor2'::text, tablespace "tablespace_index"
    "testtable_nodeid_eleanor3_idx" btree (nodeid) WHERE nodeid = 'eleanor3'::text, tablespace "tablespace_index"

Explain Plan with enable_seqscan='on'
testdb=# explain analyze select max ( auditrecor0_.sequenceNumber ) AS col_0_0_ From testdb auditrecor0_ where auditrecor0_.nodeid = 'eleanor1';
                                                                      QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
-------------------------
 Aggregate  (cost=18291486.05..18291486.06 rows=1 width=8) (actual time=484907.446..484907.446 rows=1 loops=1)
   ->  Seq Scan on testdb auditrecor0_  (cost=0.00..18147465.00 rows=57608421 width=8) (actual time=0.166..473959.12
6 rows=57801797 loops=1)
         Filter: (nodeid = 'eleanor1'::text)
         Rows Removed by Filter: 126233820
 Total runtime: 484913.013 ms
(5 rows)

Explain Plan with enable_seqscan='off'
testdb=# explain analyze select max ( auditrecor0_.sequenceNumber ) AS col_0_0_ From testdb auditrecor0_ where auditrecor0_.nodeid = 'eleanor3';
                                                                                  QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------
 Aggregate  (cost=19226040.50..19226040.51 rows=1 width=8) (actual time=388293.245..388293.245 rows=1 loops=1)
   ->  Bitmap Heap Scan on testdb auditrecor0_  (cost=2291521.32..19046381.97 rows=71863412 width=8) (actual time=15626.372..375378.362 rows=71
412687 loops=1)
         Recheck Cond: (nodeid = 'eleanor3'::text)
         Rows Removed by Index Recheck: 900820
         ->  Bitmap Index Scan on testdb_nodeid_eleanor3_idx  (cost=0.00..2273555.47 rows=71863412 width=0) (actual time=15503.465..15503.465 r
ows=71412687 loops=1)
               Index Cond: (nodeid = 'eleanor3'::text)
 Total runtime: 388294.378 ms
(7 rows)


Thanks!

--
Regards,
Ang Wei Shan

Re: Query not using Index

From
Andreas Kretschmer
Date:
Wei Shan <weishan.ang@gmail.com> wrote:

> Hi all,
>
> Please provide some advise on the following query not using the index:
> I have 2 questions:
>
>  1. Why does the optimizer chose not to use the index when it will run faster?

because of the estimated costs.:

Seq Scan on testdb auditrecor0_  (cost=0.00..18147465.00
Bitmap Heap Scan on testdb auditrecor0_  (cost=2291521.32..19046381.97

The estimated costs for the index-scan are higher.


>  2. How do I ensure the optimizer will use the index without setting
>     enable_seqscan='off'

You have a dedicated tablespace for indexes, is this a SSD? You can try
to reduce the random_page_cost, from default 4 to maybe 2.(depends on
hardware) This would reduce the estimated costs for the Index-scan and
prefer the index-scan.



Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Query not using Index

From
Wei Shan
Date:
Hi Andreas,

The tablespace is not on SSD although I intend to do it within the next week. I actually tried reducing the random_page_cost to 0.2 but it doesn't help.

On 26 March 2016 at 22:13, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
Wei Shan <weishan.ang@gmail.com> wrote:

> Hi all,
>
> Please provide some advise on the following query not using the index:
> I have 2 questions:
>
>  1. Why does the optimizer chose not to use the index when it will run faster?

because of the estimated costs.:

Seq Scan on testdb auditrecor0_  (cost=0.00..18147465.00
Bitmap Heap Scan on testdb auditrecor0_  (cost=2291521.32..19046381.97

The estimated costs for the index-scan are higher.


>  2. How do I ensure the optimizer will use the index without setting
>     enable_seqscan='off'

You have a dedicated tablespace for indexes, is this a SSD? You can try
to reduce the random_page_cost, from default 4 to maybe 2.(depends on
hardware) This would reduce the estimated costs for the Index-scan and
prefer the index-scan.



Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--
Regards,
Ang Wei Shan

Re: Query not using Index

From
Jeff Janes
Date:
On Sun, Mar 27, 2016 at 9:12 AM, Wei Shan <weishan.ang@gmail.com> wrote:
> Hi Andreas,
>
> The tablespace is not on SSD although I intend to do it within the next
> week. I actually tried reducing the random_page_cost to 0.2 but it doesn't
> help.

Setting random_page_cost to less than seq_page_cost is nonsensical.

You could try to increase cpu_tuple_cost to 0.015 or 0.02


Cheers,

Jeff