Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ajo4V-0003gv-M2 for pgsql-performance@arkaria.postgresql.org; Sat, 26 Mar 2016 13:16:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ajo4U-0005Mb-GT for pgsql-performance@arkaria.postgresql.org; Sat, 26 Mar 2016 13:16:06 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ajo2m-0003Sm-Q2 for pgsql-performance@postgresql.org; Sat, 26 Mar 2016 13:14:21 +0000 Received: from mail-io0-x230.google.com ([2607:f8b0:4001:c06::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1ajo2j-0006Mt-JY for pgsql-performance@postgresql.org; Sat, 26 Mar 2016 13:14:18 +0000 Received: by mail-io0-x230.google.com with SMTP id 124so135533451iov.3 for ; Sat, 26 Mar 2016 06:14:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:date:message-id:subject:from:to; bh=+preTPO6D26iAz+1/eBR7TPhRuAxKMgIMcOFkUYEHw0=; b=m61wWriwOKy4Nd1LiILEG96dFe80KNlbQ4GelnksiX3I8ppa/0/oLLN83yjux1h06a YV1qVCYPrAyqcq8SEG+suAD0Jc3lRbwyC5S3craFTM85p32I4F4cqP2UNz2y9a85VekQ V498FcOrd/hcC736B0SkVhonm0RT1uZwnDF2qhq62aF7bElFhN5hykU7vfgmT9n5IsQz TFRWsVmPvKCXc2hOhGnON13H6q0+2KQVoe4+yVKwvWTu4ZooZA1l6oYETJDuNFT5zo4b zqRg9t9Hn4DljsjuPJ/2sMGXoIG+OwSrgxpXvWM/1y9ReYFhlzg52adLppdtJgar3r50 1H9w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:date:message-id:subject:from:to; bh=+preTPO6D26iAz+1/eBR7TPhRuAxKMgIMcOFkUYEHw0=; b=QFTNwm230XjDd18WhDjRb8mYqP7ub02WlNkmdy7TAF8SMQ6RqJW4KvsSuWGWt/5vJT SLVAHPnw5YZGv7G4ZSZu3Sw7iWtE3VYS7qS3gfMXUe6y8r7iwhoa8t1HzmwKXz8h8FHU AW/i8W2GBf54F0TcS6AGEpWYnud3h6SxvixjKzh2HYpEU7qQ1TgEfSAU+T4AoYMjVGDX WnOfhuH/bvtBFf9AxH7ypZeoMC1e9FN6eRqZgQQ6F65OSttNIFd/r5pnev40dPYidhuf nixET/sCvsFIM+oOCYldBeoPfD/nnybp52wRG2eUDuhWZRciegqLPQhScmgI64Yh9rIb rgcQ== X-Gm-Message-State: AD7BkJLd6NO+Oj+Rv+VaPwc+UvE/wuwO9TXDiPo3Aa9Ka0Th7VMYs3W62yz+PPbBAnZCtHoxo36Nlwypu3T/PA== MIME-Version: 1.0 X-Received: by 10.107.157.70 with SMTP id g67mr16776226ioe.38.1458998056005; Sat, 26 Mar 2016 06:14:16 -0700 (PDT) Received: by 10.79.29.195 with HTTP; Sat, 26 Mar 2016 06:14:15 -0700 (PDT) Date: Sat, 26 Mar 2016 21:14:15 +0800 Message-ID: Subject: Query not using Index From: Wei Shan To: "pgsql-performance@postgresql.org list" Content-Type: multipart/alternative; boundary=001a1140b472db3450052ef37458 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a1140b472db3450052ef37458 Content-Type: text/plain; charset=UTF-8 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 --001a1140b472db3450052ef37458 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi all,

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

pgsql vers= ion: 9.2.4
OS version: RedHat 6.5
Ram: 64 GB
= rows in testdb: 180 million
shared_buffers: 16GB
effect= ive_cache_size: 32GB
work_mem=3D'32MB'

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

I have 2 questions:
  1. Why does the o= ptimizer 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= =3D'off'

Table structure.<= /u>
testdb=3D# \d testtable
=C2=A0 =C2=A0 =C2= =A0Table "public.testtable"
=C2=A0 =C2=A0 =C2=A0 Column= =C2=A0 =C2=A0 =C2=A0 | =C2=A0Type =C2=A0 | Modifiers
-----------= --------+---------+-----------
=C2=A0pk =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 =C2=A0| not null
=C2= =A0additionaldetails | text =C2=A0 =C2=A0|
=C2=A0authtoken =C2=A0= =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0| not null
=C2=A0custom= id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 =C2=A0|
=C2=A0= eventstatus =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0| not null
= =C2=A0eventtype =C2=A0 =C2=A0 =C2=A0 =C2=A0 | text =C2=A0 =C2=A0| not null<= /div>
=C2=A0module =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2= =A0 =C2=A0| not null
=C2=A0nodeid =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| text =C2=A0 =C2=A0| not null
=C2=A0rowprotection =C2= =A0 =C2=A0 | text =C2=A0 =C2=A0|
=C2=A0rowversion =C2=A0 =C2=A0 = =C2=A0 =C2=A0| integer | not null
=C2=A0searchdetail1 =C2=A0 =C2= =A0 | text =C2=A0 =C2=A0|
=C2=A0searchdetail2 =C2=A0 =C2=A0 | tex= t =C2=A0 =C2=A0|
=C2=A0sequencenumber =C2=A0 =C2=A0| bigint =C2= =A0| not null
=C2=A0service =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = text =C2=A0 =C2=A0| not null
=C2=A0timestamp =C2=A0 =C2=A0 =C2=A0= =C2=A0 | bigint =C2=A0| not null
Indexes:
=C2=A0 =C2= =A0 "testtable_pkey" PRIMARY KEY, btree (pk)
=C2=A0 =C2= =A0 "testtable_nodeid_eleanor1_idx" btree (nodeid) WHERE nodeid = =3D 'eleanor1'::text, tablespace "tablespace_index"
=
=C2=A0 =C2=A0 "testtable_nodeid_eleanor2_idx" btree (nodeid)= WHERE nodeid =3D 'eleanor2'::text, tablespace "tablespace_ind= ex"
=C2=A0 =C2=A0 "testtable_nodeid_eleanor3_idx" = btree (nodeid) WHERE nodeid =3D 'eleanor3'::text, tablespace "= tablespace_index"

Explain Plan wi= th enable_seqscan=3D'on'
testdb=3D# explain = analyze select max ( auditrecor0_.sequenceNumber ) AS col_0_0_ From testdb = auditrecor0_ where auditrecor0_.nodeid =3D 'eleanor1';
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN

--------------= ---------------------------------------------------------------------------= ------------------------------------
-------------------------
=C2=A0Aggregate =C2=A0(cost=3D18291486.05..18291486.06 rows=3D1 wid= th=3D8) (actual time=3D484907.446..484907.446 rows=3D1 loops=3D1)
=C2=A0 =C2=A0-> =C2=A0Seq Scan on testdb auditrecor0_ =C2=A0(cost=3D0.0= 0..18147465.00 rows=3D57608421 width=3D8) (actual time=3D0.166..473959.12
6 rows=3D57801797 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0Filter: (nodeid =3D 'eleanor1'::text)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Filter: 126233820
=C2=A0T= otal runtime: 484913.013 ms
(5 rows)

Explain Plan with enable_seqscan=3D'off'
=
testdb=3D# explain analyze select max ( auditrecor0_.s= equenceNumber ) AS col_0_0_ From testdb auditrecor0_ where auditrecor0_.nod= eid =3D 'eleanor3';
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
<= br>
--------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------
----------------------
=C2=A0Aggregate =C2=A0(cost=3D19226040.50..19226040.51 ro= ws=3D1 width=3D8) (actual time=3D388293.245..388293.245 rows=3D1 loops=3D1)=
=C2=A0 =C2=A0-> =C2=A0Bitmap Heap Scan on testdb a= uditrecor0_ =C2=A0(cost=3D2291521.32..19046381.97 rows=3D71863412 width=3D8= ) (actual time=3D15626.372..375378.362 rows=3D71
41268= 7 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Recheck= Cond: (nodeid =3D 'eleanor3'::text)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Removed by Index Recheck: 900820
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Bitmap Index Scan = on testdb_nodeid_eleanor3_idx =C2=A0(cost=3D0.00..2273555.47 rows=3D7186341= 2 width=3D0) (actual time=3D15503.465..15503.465 r
ows= =3D71412687 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (nodeid =3D 'eleanor3'::text)
=C2=A0Total runtime: 388294.378 ms
(7 rows)


Thanks!

--
Regards,
Ang = Wei Shan
--001a1140b472db3450052ef37458--