Styjun

Are we obligated to aspire to be Talmidei Chachamim?

I caught several of my students plagiarizing. Could it be my fault as a teacher?

Should I get informal with a professor if they didn't explicitly invite me to do so but the situation seems to suggest it's appropriate?

How to improve/restore vintage Peugeot bike, or is it even worth it?

Junior developer struggles: how to communicate with management?

How did Arya get her dagger back from Sansa?

Linear algebra - find dual space base

In Avengers 1, why does Thanos need Loki?

Why is the "alignment" the same on 32-bit and 64-bit systems?

Set command option with document wide newcommand

Was there ever a Kickstart that took advantage of 68020+ instructions that would work on an A2000?

What algorithms are considered reinforcement learning algorithms?

Number of seconds in 6 weeks

What is the minimal installation possible in order to run a .jar Java file?

Point of the the Dothraki's attack in GoT S8E3?

Is it possible to set that path of the scp command to use by OpenSSH sshd daemon?

Would glacier 'trees' be plausible?

Besides the up and down quark, what other quarks are present in daily matter around us?

How to generate a customised histogram with pgf plots?

How can I support myself financially as a 17 year old with a loan?

Is Jon Snow immune to dragonfire?

Enumerate Derangements

FindInstance and cosine system of equations

If Earth is tilted, why is Polaris always above the same spot?

Postgres Indexes isn't being utilized

How does database indexing work?How to exit from PostgreSQL command line utility: psqlpostgres: upgrade a user to be a superuser?What does the `width` field mean in PostgreSQL's EXPLAIN?Postgres query optimization (forcing an index scan)indexes for large database in postgresIndex doesn't improve performanceunaccent() preventing index usage in PostgresWhy isn't Postgres using the index with Distinct?Index using `date_trunc` isn't used automatically

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

New to Postgres, and not very familiar with how RDBMS work in general. I read that, in certain cases, adding an index to a table speeds up query performance for databases. I tried it out with a table and did so (Postgres v11.2):

CREATE TABLE testtable(
 idString text,
 comment text
);


INSERT INTO 
 testtable(idString, comment)
VALUES
 ('1:2', 'some text'),
 ('12:2', 'blah'),
 ('2:2', 'other text'),
 ('1:3', 'blah'),
 ('33:2', 'blah');


CREATE INDEX myindex ON testtable(idString asc);

The guide I was reading said that, without an index, the database usually does a "sequential scan" of all entries until the query is found, but with an index, it does an "index scan". The guide says to see the query plan using "EXPLAIN", so I do:

EXPLAIN SELECT * FROM testtable WHERE myid = '1:3';

The output, however still seems to be a sequential scan:

 QUERY PLAN
----------------------------------------------------------
 Seq Scan on testtable (cost=0.00..1.07 rows=1 width=64)
 Filter: (myid = '1:3'::text)
(2 rows)

I've checked using pgAdmin and see that myindex does exist, but I'm unsure why the database isn't using it? Is there something else that I'm missing/haven't done?

sql postgresql indexing

share|improve this question

edited Mar 22 at 21:06

Gordon Linoff

804k37323430

asked Mar 22 at 21:04

albertalbert

330314

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  You don't have enough rows to make the index worthwhile.
  
  – Gordon Linoff
  Mar 22 at 21:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Right this was just a test table, can't tell if your comment is just a general comment, or that's the reason the index doesn't exist. If the latter, how many rows does a table have to have before the index is utilized?
  
  – albert
  Mar 22 at 21:09
  

  
  
  
  

add a comment | 

0

New to Postgres, and not very familiar with how RDBMS work in general. I read that, in certain cases, adding an index to a table speeds up query performance for databases. I tried it out with a table and did so (Postgres v11.2):

CREATE TABLE testtable(
 idString text,
 comment text
);


INSERT INTO 
 testtable(idString, comment)
VALUES
 ('1:2', 'some text'),
 ('12:2', 'blah'),
 ('2:2', 'other text'),
 ('1:3', 'blah'),
 ('33:2', 'blah');


CREATE INDEX myindex ON testtable(idString asc);

The guide I was reading said that, without an index, the database usually does a "sequential scan" of all entries until the query is found, but with an index, it does an "index scan". The guide says to see the query plan using "EXPLAIN", so I do:

EXPLAIN SELECT * FROM testtable WHERE myid = '1:3';

The output, however still seems to be a sequential scan:

 QUERY PLAN
----------------------------------------------------------
 Seq Scan on testtable (cost=0.00..1.07 rows=1 width=64)
 Filter: (myid = '1:3'::text)
(2 rows)

I've checked using pgAdmin and see that myindex does exist, but I'm unsure why the database isn't using it? Is there something else that I'm missing/haven't done?

sql postgresql indexing

share|improve this question

edited Mar 22 at 21:06

Gordon Linoff

804k37323430

asked Mar 22 at 21:04

albertalbert

330314

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  You don't have enough rows to make the index worthwhile.
  
  – Gordon Linoff
  Mar 22 at 21:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Right this was just a test table, can't tell if your comment is just a general comment, or that's the reason the index doesn't exist. If the latter, how many rows does a table have to have before the index is utilized?
  
  – albert
  Mar 22 at 21:09
  

  
  
  
  

add a comment | 

New to Postgres, and not very familiar with how RDBMS work in general. I read that, in certain cases, adding an index to a table speeds up query performance for databases. I tried it out with a table and did so (Postgres v11.2):

CREATE TABLE testtable(
 idString text,
 comment text
);


INSERT INTO 
 testtable(idString, comment)
VALUES
 ('1:2', 'some text'),
 ('12:2', 'blah'),
 ('2:2', 'other text'),
 ('1:3', 'blah'),
 ('33:2', 'blah');


CREATE INDEX myindex ON testtable(idString asc);

The guide I was reading said that, without an index, the database usually does a "sequential scan" of all entries until the query is found, but with an index, it does an "index scan". The guide says to see the query plan using "EXPLAIN", so I do:

EXPLAIN SELECT * FROM testtable WHERE myid = '1:3';

The output, however still seems to be a sequential scan:

 QUERY PLAN
----------------------------------------------------------
 Seq Scan on testtable (cost=0.00..1.07 rows=1 width=64)
 Filter: (myid = '1:3'::text)
(2 rows)

I've checked using pgAdmin and see that myindex does exist, but I'm unsure why the database isn't using it? Is there something else that I'm missing/haven't done?

sql postgresql indexing

share|improve this question

edited Mar 22 at 21:06

Gordon Linoff

804k37323430

asked Mar 22 at 21:04

albertalbert

330314

CREATE TABLE testtable(
 idString text,
 comment text
);


INSERT INTO 
 testtable(idString, comment)
VALUES
 ('1:2', 'some text'),
 ('12:2', 'blah'),
 ('2:2', 'other text'),
 ('1:3', 'blah'),
 ('33:2', 'blah');


CREATE INDEX myindex ON testtable(idString asc);

EXPLAIN SELECT * FROM testtable WHERE myid = '1:3';

 QUERY PLAN
----------------------------------------------------------
 Seq Scan on testtable (cost=0.00..1.07 rows=1 width=64)
 Filter: (myid = '1:3'::text)
(2 rows)

share|improve this question

edited Mar 22 at 21:06

Gordon Linoff

804k37323430

asked Mar 22 at 21:04

albertalbert

330314

share|improve this question

edited Mar 22 at 21:06

Gordon Linoff

804k37323430

asked Mar 22 at 21:04

albertalbert

330314

edited Mar 22 at 21:06

Gordon Linoff

804k37323430

edited Mar 22 at 21:06

Gordon Linoff

804k37323430

asked Mar 22 at 21:04

albertalbert

330314

asked Mar 22 at 21:04

albertalbert

330314

1 Answer
1

active

oldest

votes

3

Databases take many factors into consideration when deciding to use an index.

Your query is:

SELECT *
FROM testtable
WHERE myid = '1:3';

There are basically two reasonable approaches:

The first is to scan the data, and apply the WHERE clause to each row.

The second is to lookup the value in the index and then fetch the rest of the data.

Which is cheaper? In your case, the first is cheaper. Why? Only one page needs to be moved from tertiary storage into memory. Scanning the page -- after doing all the work of loading it -- is pretty cheap.

Using the index requires loading two pages, one for the index and one for the data.

Although database optimization is complicated, this is a simple example to give you a flavor of the different methods used in optimization and the trade-offs.

share|improve this answer

answered Mar 22 at 21:10

Gordon LinoffGordon Linoff

804k37323430

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Still a bit confused. You're saying the second is cheaper, as in the database somehow decides this, right? If so, how does it decide that the second is cheaper?
  
  – albert
  Mar 22 at 21:13
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @albert This is a huge topic in its own right, but basically, the database has a cost model associated with different physical operations such as scanning the table or seeking the index. It then considers various physical operations that accomplish the same logical operation and picks one with the lowest cost.
  
  – Branko Dimitrijevic
  Mar 22 at 21:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ah ok I get it now, so the database determines which one to use. Then, as long as I create the index for the table, I can rest safe knowing that the database will use the index when optimal?
  
  – albert
  Mar 22 at 21:22
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @albert In theory yes, and in this simple case almost certainly. In more complex cases, this so called "cost-based query optimization" mechanism may not work ideally, which is why this is a big topic! :)
  
  – Branko Dimitrijevic
  Mar 22 at 21:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @BrankoDimitrijevic That makes sense, thanks so much!
  
  – albert
  Mar 22 at 21:28
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55307747%2fpostgres-indexes-isnt-being-utilized%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

Databases take many factors into consideration when deciding to use an index.

Your query is:

SELECT *
FROM testtable
WHERE myid = '1:3';

There are basically two reasonable approaches:

The first is to scan the data, and apply the WHERE clause to each row.

The second is to lookup the value in the index and then fetch the rest of the data.

Which is cheaper? In your case, the first is cheaper. Why? Only one page needs to be moved from tertiary storage into memory. Scanning the page -- after doing all the work of loading it -- is pretty cheap.

Using the index requires loading two pages, one for the index and one for the data.

Although database optimization is complicated, this is a simple example to give you a flavor of the different methods used in optimization and the trade-offs.

share|improve this answer

answered Mar 22 at 21:10

Gordon LinoffGordon Linoff

804k37323430

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Still a bit confused. You're saying the second is cheaper, as in the database somehow decides this, right? If so, how does it decide that the second is cheaper?
  
  – albert
  Mar 22 at 21:13
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @albert This is a huge topic in its own right, but basically, the database has a cost model associated with different physical operations such as scanning the table or seeking the index. It then considers various physical operations that accomplish the same logical operation and picks one with the lowest cost.
  
  – Branko Dimitrijevic
  Mar 22 at 21:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ah ok I get it now, so the database determines which one to use. Then, as long as I create the index for the table, I can rest safe knowing that the database will use the index when optimal?
  
  – albert
  Mar 22 at 21:22
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @albert In theory yes, and in this simple case almost certainly. In more complex cases, this so called "cost-based query optimization" mechanism may not work ideally, which is why this is a big topic! :)
  
  – Branko Dimitrijevic
  Mar 22 at 21:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @BrankoDimitrijevic That makes sense, thanks so much!
  
  – albert
  Mar 22 at 21:28
  

  
  
  
  

add a comment | 

3

Databases take many factors into consideration when deciding to use an index.

Your query is:

SELECT *
FROM testtable
WHERE myid = '1:3';

There are basically two reasonable approaches:

The first is to scan the data, and apply the WHERE clause to each row.

The second is to lookup the value in the index and then fetch the rest of the data.

Which is cheaper? In your case, the first is cheaper. Why? Only one page needs to be moved from tertiary storage into memory. Scanning the page -- after doing all the work of loading it -- is pretty cheap.

Using the index requires loading two pages, one for the index and one for the data.

Although database optimization is complicated, this is a simple example to give you a flavor of the different methods used in optimization and the trade-offs.

share|improve this answer

answered Mar 22 at 21:10

Gordon LinoffGordon Linoff

804k37323430

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Still a bit confused. You're saying the second is cheaper, as in the database somehow decides this, right? If so, how does it decide that the second is cheaper?
  
  – albert
  Mar 22 at 21:13
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @albert This is a huge topic in its own right, but basically, the database has a cost model associated with different physical operations such as scanning the table or seeking the index. It then considers various physical operations that accomplish the same logical operation and picks one with the lowest cost.
  
  – Branko Dimitrijevic
  Mar 22 at 21:20
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ah ok I get it now, so the database determines which one to use. Then, as long as I create the index for the table, I can rest safe knowing that the database will use the index when optimal?
  
  – albert
  Mar 22 at 21:22
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @albert In theory yes, and in this simple case almost certainly. In more complex cases, this so called "cost-based query optimization" mechanism may not work ideally, which is why this is a big topic! :)
  
  – Branko Dimitrijevic
  Mar 22 at 21:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @BrankoDimitrijevic That makes sense, thanks so much!
  
  – albert
  Mar 22 at 21:28
  

  
  
  
  

add a comment | 

Databases take many factors into consideration when deciding to use an index.

Your query is:

SELECT *
FROM testtable
WHERE myid = '1:3';

There are basically two reasonable approaches:

The first is to scan the data, and apply the WHERE clause to each row.

The second is to lookup the value in the index and then fetch the rest of the data.

Which is cheaper? In your case, the first is cheaper. Why? Only one page needs to be moved from tertiary storage into memory. Scanning the page -- after doing all the work of loading it -- is pretty cheap.

Using the index requires loading two pages, one for the index and one for the data.

Although database optimization is complicated, this is a simple example to give you a flavor of the different methods used in optimization and the trade-offs.

share|improve this answer

answered Mar 22 at 21:10

Gordon LinoffGordon Linoff

804k37323430

SELECT *
FROM testtable
WHERE myid = '1:3';

share|improve this answer

answered Mar 22 at 21:10

Gordon LinoffGordon Linoff

804k37323430

answered Mar 22 at 21:10

Gordon LinoffGordon Linoff

804k37323430

answered Mar 22 at 21:10

Gordon LinoffGordon Linoff

804k37323430