Deal of The Day! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions
Mail Us support@pass4success.com
Location US
Mob_nav_barsMENU

PostgreSQL PGCES-02 Exam - Topic 10 Question 50 Discussion

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.
A) CREATE INDEX t1_idx ON t1 (id, upper(name));
B) CREATE INDEX t1_idx ON t1 USING HASH (id);
C) CREATE INDEX t1_idx ON t1 (name);
D) ALTER TABLE ADD INDEX ON t1 (id, upper(name));
E) ALTER TABLE ADD INDEX ON t1 (id, name);

PostgreSQL PGCES-02 Exam - Topic 10 Question 50 Discussion

Actual exam question for PostgreSQL's PGCES-02 exam
Question #: 50
Topic #: 10
[All PGCES-02 Questions]

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.

Show Suggested Answer Hide 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:

0/2000 characters
Submit Cancel
Kanisha
7 months ago
Wait, can you really index upper(name)? Sounds tricky!
upvoted 0 times
...
Cortney
8 months ago
C is a no-go, it won't help with the WHERE clause.
upvoted 0 times
...
Dorinda
8 months ago
I think B might work too, but not sure.
upvoted 0 times
...
Casey
8 months ago
Totally agree, A makes the most sense!
upvoted 0 times
...
Ceola
8 months ago
Option A is the best choice for this query.
upvoted 0 times
...
Cyril
9 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
9 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
9 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
9 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
9 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
9 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
9 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
9 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
9 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
9 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
9 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
10 months ago
I'm pretty sure Scrum doesn't have a "Project Manager" role, so I'll go with B.
upvoted 0 times
...
Lenita
10 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
1 year ago
Option C? I guess the database admin just has a 'name' fetish. But hey, whatever floats your boat!
upvoted 0 times
...
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?
upvoted 0 times
Arlene
1 year ago
E) ALTER TABLE ADD INDEX ON t1 (id, name);
upvoted 0 times
...
Alexis
1 year ago
C) CREATE INDEX t1_idx ON t1 (name);
upvoted 0 times
...
Annmarie
1 year ago
B) CREATE INDEX t1_idx ON t1 USING HASH (id);
upvoted 0 times
...
Bernardine
1 year ago
A) CREATE INDEX t1_idx ON t1 (id, upper(name));
upvoted 0 times
...
...
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.
upvoted 0 times
...
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.
upvoted 0 times
Rasheeda
1 year ago
User3: I agree with User1, Option D seems like the best choice.
upvoted 0 times
...
Emily
1 year ago
User2: I'm not sure, Option A might be more efficient.
upvoted 0 times
...
Carmela
1 year ago
User1: I think Option D is the way to go.
upvoted 0 times
...
...
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.
upvoted 0 times
Ellsworth
1 year ago
I'm not sure, but Option B could also be a good option to consider for improving execution speed.
upvoted 0 times
...
Jerry
1 year ago
I think Option A might be better, as it indexes both the ID and name columns together.
upvoted 0 times
...
Tamie
1 year ago
I agree with you, Option E seems like the best choice for this scenario.
upvoted 0 times
...
...
Ettie
1 year ago
But creating an index on name column only, like option C, might not be efficient for this specific query.
upvoted 0 times
...
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.
upvoted 0 times
Adelle
1 year ago
B) CREATE INDEX t1_idx ON t1 USING HASH (id);
upvoted 0 times
...
Skye
1 year ago
A) CREATE INDEX t1_idx ON t1 (id, upper(name));
upvoted 0 times
...
Ezekiel
1 year ago
B) CREATE INDEX t1_idx ON t1 USING HASH (id);
upvoted 0 times
...
Tasia
1 year ago
A) CREATE INDEX t1_idx ON t1 (id, upper(name));
upvoted 0 times
...
...
Erasmo
1 year ago
I disagree, I believe option B is better as it uses HASH which can improve performance.
upvoted 0 times
...
Ettie
1 year ago
I think option A is the best choice because it creates an index on both id and upper(name) columns.
upvoted 0 times
...

Save Cancel