Discuss PostgreSQL PGCES-02 Exam Topic 10 Question 50

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);

Suggested Answer: A

Get Premium PGCES-02 Questions as Interactive Web-Based Practice Test or PDF Real Exam Format Detailed Explanations Instant Access Get Your Practice Test

41 comments · newest first

Kanisha 10 months ago

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

Cortney 11 months ago

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

Dorinda 11 months ago

I think B might work too, but not sure.

Casey 11 months ago

Totally agree, A makes the most sense!

Ceola 11 months ago

Option A is the best choice for this query.

Cyril 12 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.

Esteban 12 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.

Olen 12 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.

Annamae 1 year ago

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

Rodrigo 1 year 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.

Salena 1 year 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.

Ilene 1 year 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.

Barbra 1 year 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.

Tien 1 year 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.

Stephen 1 year 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.

Clorinda 1 year 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.

Reiko 1 year ago

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

Lenita 1 year 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.

Levi 1 year ago

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

Mike 1 year 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?

Arlene 1 year ago

↳ replying to Mike

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

Alexis 1 year ago

↳ replying to Mike

C) CREATE INDEX t1_idx ON t1 (name);

Annmarie 1 year ago

↳ replying to Mike

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

Bernardine 1 year ago

↳ replying to Mike

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

Deonna 1 year 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.

Rima 1 year 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.

Rasheeda 1 year ago

↳ replying to Rima

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

Emily 1 year ago

↳ replying to Rima

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

Carmela 1 year ago

↳ replying to Rima

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

Paris 1 year 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.

Ellsworth 1 year ago

↳ replying to Paris

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

Jerry 1 year ago

↳ replying to Paris

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

Tamie 1 year ago

↳ replying to Paris

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

Ettie 1 year ago

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

Olive 1 year 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.

Adelle 1 year ago

↳ replying to Olive

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

Skye 1 year ago

↳ replying to Olive

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

Ezekiel 1 year ago

↳ replying to Olive

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

Tasia 1 year ago

↳ replying to Olive

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

Erasmo 2 years ago

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

Ettie 2 years ago

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

PostgreSQL PGCES-02 Exam - Topic 10 Question 50 Discussion