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

Levi

15 days ago

Option C? I guess the database admin just has a 'name' fetish. But hey, whatever floats your boat!

upvoted 0 times

...

Mike

16 days ago

I'd suggest creating an index on the name column, and then just waiting patiently for the 'Mammoth' to show up. Slow and steady wins the race, right?

upvoted 0 times

Annmarie

1 days ago

B) CREATE INDEX t1_idx ON t1 USING HASH (id);

upvoted 0 times

...

Bernardine

2 days ago

A) CREATE INDEX t1_idx ON t1 (id, upper(name));

upvoted 0 times

...

Deonna

18 days ago

Hmm, Option B using a hash index doesn't seem like the best fit here since we're looking for range-based filtering on the ID column.

upvoted 0 times

...

Rima

20 days ago

I'm going with Option D. Altering the table to add an index on the ID and UPPER(name) columns seems like a straightforward solution to the problem.

upvoted 0 times

...

Paris

28 days ago

I think Option E is the way to go. Indexing both the ID and name columns will provide a good balance between query performance and index maintenance overhead.

upvoted 0 times

Jerry

13 days ago

I think Option A might be better, as it indexes both the ID and name columns together.

upvoted 0 times

...

Tamie

20 days ago

I agree with you, Option E seems like the best choice for this scenario.

upvoted 0 times

...

Ettie

2 months ago

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

upvoted 0 times

...

Olive

2 months 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