Book Store Indexes PostgreSQL Project

Hello, I’ve just come onto this project and I seem to have encountered an error. I have reloaded my browser, even shut down and restart my PC, unfortunately there seems to be an issue with the Codecademy Server.

I keep getting this error message whenever I try and run the terminal. No matter if I have a blank editor or full of SELECT *s.

cp: cannot create regular file ‘setup.sql’: Disk quota exceeded

I’m guessing that there’s a problem with Codecademy server side struggling with an overloaded sandbox.

Thanks.

1 Like

I’m having an issue with this lesson hanging on the first step.

1 Like

Hello @psmilliorn,

Are you still having an issue with this lesson?

Please post a link to the lesson to help you troubleshoot.

1 Like

Not an issue on my end. It’s the lesson itself hanging. As for the lesson it was Book Store Indexes PostgreSQL Project. If you try to save any work the page hangs. It does not do that for me on any page here on this site. I have no browsing issues at all on my browser. I just moved on because this is obviously broken and not maintained(which is a shame because people spend money on this to work).

3 Likes

Quick question here, with the questions 3-5 regarding the partial index. Was it meant to decrease the query time significantly? Mine doesn’t seem to do so.

Here is the code I used to do the index as well as the query:

explain analyze select customers.customer_id, orders.quantity
from customers
join orders
on customers.customer_id = orders.customer_id
where quantity > 18;

create index order_quantity_idx on 
orders(quantity)
where quantity > 18;

explain analyze select customers.customer_id, orders.quantity
from customers
join orders
on customers.customer_id = orders.customer_id
where quantity > 18;

(I know that the SQL needs to be capitalized, but for the sake of efficiency I don’t bother)

and these are the planning and execution times for pre and post indexing respectively:

Planning time: 4.432 ms
Execution time: 92.872 ms

Planning time: 0.178 ms
Execution time: 77.399 ms

Thanks.

2 Likes

Hi @css8331182305 ,

It looks like you got significantly better query time than me.

These were my results:

Planning time: 0.023 ms
Execution time: 10.695 ms
---
Planning time: 0.147 ms
Execution time: 3.442 ms
1 Like

@coffeencake Thanks for the reply…

So I’m assuming that my queries were correct? Also, aren’t your query times MUCH better than mine - is the difference in query times due to server latency?

Thanks!

1 Like

:laughing: Yes, you are correct. (I must have been partially asleep when I read the numbers.)

I’m not sure why there is such a large difference in query times. There’s many other factors that can impact the explain analyze. If another process was querying the table at the same time while you were running your explain analyze, that would have an impact. Yes, it’s possible server latency would impact the query times.

2 Likes

Hello everyone,

I’m not sure what to do in step 9:

“EXPLAIN ANALYZE doesn’t work on the COPY` call, so we are using a timestamp before and after we load the information into the database. Make a note of the time difference between the two timestamps as they will get erased in the next task.”
I’m writing this code to compare the two timestamps, please who can help in case the aim of step 9 was something different:

EXPLAIN ANALYZE SELECT * FROM orders;
SELECT NOW();
\COPY orders FROM ‘orders_add.txt’ DELIMITER ‘,’ CSV HEADER;
SELECT NOW();
EXPLAIN ANALYZE SELECT * FROM orders;

1 Like

Hello @b80, can you include a link to the lesson please? I can try to step thru the lesson to help answer your question. I tried the above but it doesn’t reference the lesson.

1 Like

Hi coffeencake, thank you for replying, here is the link:

https://www.codecademy.com/paths/computer-science/tracks/cspath-databases/modules/cspath-how-do-i-make-sure-my-database-stays-fast/projects/indexes-part-1-project

1 Like

Hello @b80 ,

You won’t be able to use EXPLAIN ANALYZE to check the runtime query for COPY. Use the code exactly as is. It’s show to you get the time for before and after the query to determine how long the COPY will run. The different in the before time and after time is how long the copy took to run.

SELECT NOW();  -- before timestamp
 
\COPY orders FROM 'orders_add.txt' DELIMITER ',' CSV HEADER;
 
SELECT NOW(); -- after timestamp
1 Like

Still getting the “cp: cannot create regular file ‘setup.sql’: Disk quota exceeded” error which doesn’t allow the learner to finish the project. Have tried refreshing the page and resetting the exercise, neither works.

1 Like

Hello @board7302290096 , I had success in running it just now. Give it a try.

1 Like