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

2 months ago

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

upvoted 0 times

...

Mike

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

Arlene

27 days ago

E) ALTER TABLE ADD INDEX ON t1 (id, name);

upvoted 0 times

...

Alexis

30 days ago

C) CREATE INDEX t1_idx ON t1 (name);

upvoted 0 times

...

Annmarie

2 months ago

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

upvoted 0 times

...

Bernardine

2 months ago

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

upvoted 0 times

...

Deonna

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

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

Rasheeda

22 days ago

User3: I agree with User1, Option D seems like the best choice.

upvoted 0 times

...

Emily

1 months ago

User2: I'm not sure, Option A might be more efficient.

upvoted 0 times

...

Carmela

1 months ago

User1: I think Option D is the way to go.

upvoted 0 times

...

Paris

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

Ellsworth

2 months ago

I'm not sure, but Option B could also be a good option to consider for improving execution speed.

upvoted 0 times

...

Jerry

2 months ago

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

upvoted 0 times

...

Tamie

2 months ago

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

upvoted 0 times

...

Ettie

3 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

3 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