PostgreSQL Question

Hey!

Does anybody is in-depth into PostgreSQL and help me with a little something?

I gotta write a huuuuuge Query that’s really complex and since I’m getting started with it I don’t even know how I should go about it.

So, if you are a DBA or just really good with PostgreSQL please please please hit me up!

1 Like

I’m still working on it and help would be much appreciated!

also @moderators we dont have a tag for either postgres nor databases which is kinda surprising. and you can only use 3 tags!? maybe get that limit to 5?

That’s more of a Wendell thing, we can’t control how the site works

1 Like

oh okay, in that case I’ll send him a message. Thought you guys could do that as well, sorry for bothering you.

No problem

1 Like

You can create new tags yourself. As you can see, I just did :grin:. As for the tag limit I agree, I think 6 would be a good limit, maybe 8 max.

1 Like

Whoops, I tried but failed as it seems.

Thank you!

No worries. You just need to type your tag into the field and then hit enter for it to show up. If a tag does not show up, most likely it is because you used the word of the tag in the title already. It gets removed by the forum to avoid redundancy I guess.

Sorta kinda missed the point, right?

Like I would like to have as tags in this instance:

linux helpdesk database postgres sql

If I either have a thread title that is informative I can’t have parts of it as tags? Maybe I don’t get how tags work but looks to me like that would need to change to make the use of tags actual helpful.

Why bother using tags when I can only use a small amount of them there are not closely related to the topic?

Anyway, back to the point:

someone who knows SQL in here?

I’m no SQL query writing expert but when you say big, how many tables are you working with?

You should never try and join across anymore than 7 tables - instead break the query up and use temp tables or Common Table Expressions (CTE’s) this helps the query optimizer work out a decent plan instead of timing out and giving you whatever it could come up with.

If needed you should also engage with a DBA/DB Dev to create indexes that will help your queries. There is loads of good advice out there and quite a few free ebooks etc.

1 Like

I join across at least 5 tables and the output of the query needs to be as polished as possible since the output goes directly to a customer.

I worked 3 hours today on it and I think I got it but its very slow and way too bulky so I need to put more time into it.

But I’ll look into CTEs but what is a query optimizer? A dedicated tool or some parr of the DB?

I was just being generic when I wrote query optimized, I just meant the query engine in postgres that parses your query and selects a good enough query plan.

1 Like

Oh okay, no thats actually something i didnt knew.

Ill look into that, cant hurt to really dig into postgres while im at it.

Thanks! Much appreciated.

Talking to a DBA for a performance optimized query is a good idea. Unless your where clauses/joins are on unindexed columns, there’s not much you can do. Those query planners should do a pretty good job of optimizing your query. What affects the speed of your query ultimately comes down to the size of your data and if the fields are indexed. A DBA can help with both of these issues.

1 Like

We don’t have a DBA inhouse and the only person who could help isn’t available until the next 2 weeks, so it’s on me.

I’ll see how far I get.