PostgreSQL Exam PGCES-02 Topic 10 Question 50 Discussion

Actual exam question for PostgreSQL's PGCES-02 exam

Question #: 50
Topic #: 10

The table "t1" is defined by the following SQL statement: CREATE TABLE t1 (id integer, name varchar(20)); You want to increase the execution speed of the SQL statement below: SELECT id, name FROM t1 WHERE id < 123 AND upper(name) = 'MAMMOTH'; Select the most suitable SQL statement to create an index.

ACREATE INDEX t1_idx ON t1 (id, upper(name));

BCREATE INDEX t1_idx ON t1 USING HASH (id);

CCREATE INDEX t1_idx ON t1 (name);

DALTER TABLE ADD INDEX ON t1 (id, upper(name));

EALTER TABLE ADD INDEX ON t1 (id, name);

Show Suggested Answer

Suggested Answer: A

by Viva at Oct 19, 2023, 09:59 AM

Limited Time Offer

25%

Off

Get Premium PGCES-02 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Ettie

12 days ago

But creating an index on name column only, like option C, might not be efficient for this specific query.

upvoted 0 times

...

Olive

13 days ago

Option A seems like the most suitable choice. Creating an index on the ID column and the UPPER(name) function will allow the database to quickly filter the results based on the ID range and the converted name value.

upvoted 0 times

...

Erasmo

14 days ago

I disagree, I believe option B is better as it uses HASH which can improve performance.

upvoted 0 times

...

Ettie

25 days ago

I think option A is the best choice because it creates an index on both id and upper(name) columns.

upvoted 0 times

...