PostgreSQL PGCES-02 Exam - 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

Kanisha

4 months ago

Wait, can you really index upper(name)? Sounds tricky!

upvoted 0 times

...

Cortney

4 months ago

C is a no-go, it won't help with the WHERE clause.

upvoted 0 times

...

Dorinda

4 months ago

I think B might work too, but not sure.

upvoted 0 times

...

Casey

5 months ago

Totally agree, A makes the most sense!

upvoted 0 times

...

Ceola

5 months ago

Option A is the best choice for this query.

upvoted 0 times

...

Cyril

5 months ago

I feel like option D is a bit off because it uses ALTER TABLE, which isn't how you typically create an index. I think A is still the best choice.

upvoted 0 times

...

Esteban

5 months ago

I practiced a similar question before, and I remember that creating an index on both columns is usually better for performance. So, A seems right to me.

upvoted 0 times

...

Olen

5 months ago

I'm not entirely sure about the hash index in option B. I remember something about it being faster for equality checks, but this query has a range condition too.

upvoted 0 times

...

Annamae

6 months ago

I think option A makes the most sense since it includes both id and upper(name), which are used in the WHERE clause.

upvoted 0 times

...

Rodrigo

6 months ago

This is a tricky one. I'm not sure if a hash index on just the `id` column will be sufficient, or if I need to go with a regular B-tree index on the `id` and `name` columns. I'll have to think it through carefully.

upvoted 0 times

...

Salena

6 months ago

Okay, I think I've got a good strategy here. I'll create a composite index on `(id, upper(name))` to cover both conditions in the `WHERE` clause. That should give me the best performance boost.

upvoted 0 times

...

Ilene

6 months ago

I'm a bit confused by the `upper(name)` part. Do I need to index the function call itself, or just the `name` column? I'll have to double-check the syntax for creating indexes on expressions.

upvoted 0 times

...

Barbra

6 months ago

Hmm, the key here seems to be the `WHERE` clause with the `id < 123` and `upper(name) = 'MAMMOTH'` conditions. I'll need to consider indexing both the `id` and `name` columns.

upvoted 0 times

...

Tien

6 months ago

This looks like a pretty straightforward SQL indexing question. I'll need to think carefully about which columns to index to optimize the given query.

upvoted 0 times

...

Stephen

6 months ago

Ah, I remember learning about Tanzu in class. Unified, global multi-cluster management and the enterprise-ready Kubernetes runtime are definitely two of the key capabilities. I'm a bit stuck on the third one, though.

upvoted 0 times

...

Clorinda

6 months ago

Hmm, I'm a little unsure about this one. I know the control schedule process has inputs, but I can't quite remember what they all are. I'll have to think this through carefully.

upvoted 0 times

...

Reiko

6 months ago

I'm pretty sure Scrum doesn't have a "Project Manager" role, so I'll go with B.

upvoted 0 times

...

Lenita

6 months ago

Hmm, I'm not entirely sure about this one. I'll have to re-read the question and think it through step-by-step.

upvoted 0 times

...

Levi

10 months ago

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

upvoted 0 times

...

Mike

10 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

9 months ago

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

upvoted 0 times

...

Alexis

9 months ago

C) CREATE INDEX t1_idx ON t1 (name);

upvoted 0 times

...

Annmarie

10 months ago

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

upvoted 0 times

...

Bernardine

10 months ago

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

upvoted 0 times

...

Deonna

10 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

11 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

9 months ago

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

upvoted 0 times

...

Emily

9 months ago

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

upvoted 0 times

...

Carmela

10 months ago

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

upvoted 0 times

...

Paris

11 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

10 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

10 months ago

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

upvoted 0 times

...

Tamie

11 months ago

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

upvoted 0 times

...

Ettie

11 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

12 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