mikeizbicki / cmc-csci143

big data course materials
41 stars 76 forks source link

Question about creating indexes for denormalized queries for `twitter_postgres_indexes` #555

Closed adamzterenyi closed 6 months ago

adamzterenyi commented 6 months ago

Howdy,

I am currently creating indexes within postgres for the pg_denormalized queries on the indexes homework. I have been running the command to create my first index for the previous two hours. Did anyone else experience a similar length. Just asking for a friend, lol.

Thanks.

gibsonfriedman commented 6 months ago

I'm also experiencing something similar, I was also wondering if we have to leave our computer open while waiting for the index to be created or if there is a way to run it in a similar fashion to a nohup command? @mikeizbicki I'm curious to see what you think about that possibility or if it just isn't possible to do.

adamzterenyi commented 6 months ago

The reason I ask is because I get a broken pipe and am logged out of the terminal when the index takes too long to be created.

adamzterenyi commented 6 months ago

@mikeizbicki, I was doing some research, and am wondering if I could run

CREATE INDEX CONCURRENTLY with_my_index ON whatever_table_I_am_using_it_for(column_name);

In order to be able to run my index creation statements in the background. The reason I say this is because I am timing out the terminal while trying to create my indexes, as they the statements are taking too long.

Would this be a good idea, especially because I have no reason to read or write the data?

EDIT: just tried this, and I'm unsure if it is working. Would still appreciate any info anyone has on this.

adamzterenyi commented 6 months ago

The sun has returned to my life. This worked.

ains-arch commented 6 months ago

I'm curious, how did you go about using this create index concurrently command? Did you just run it in the postgres cli in the container?

adamzterenyi commented 6 months ago

Yes, precisely. Then you can check with

# \d tweets_jsonb

whether the index is ready.