July 26, 2018 at 9:02 am
so I have the following problem.
I have three tables in my postgresql DB.
They are: package, cartype and packagecartype.
package refers to a thing that needs to be moved from A to B and has certain requirements for it. For example, one of the requirements is freightlimit, which indicates how big the car has to be that transports a package. So it has minfreight and maxfreight which makes a range, "Car needs to be able to transport at a minimum this much and at a maximum that much, for this package."
cartype is a table where I save types of cars, that has nothing more than a an id, a minfreight and maxfreight and a name.
A package can have multiple cartypes, which are saved in the table packagecartype.
It means you don't have to enter the min/max-freight for the package, instead you can give it one or multiple cartypes to indicate the freight range.
packagecartype has three columns, it's own id, the packageid and the cartypeid.
Now I want to build a search function, that make a sql statement and give me all packages within certain parameters.
One of the fields I want my search to have is minfreight and maxfreight, meaning it will give me back all packages that have min/maxfreight within the given range OR all packages that have cartypes within the given range.
My SQL statement currently looks like this:
SELECT * FROM package WHERE
((COALESCE(package.maxfreight, (SELECT MAX(ct.maxfreight)
FROM packagecartype AS pct
LEFT JOIN cartype AS ct
ON pct.cartypeid = ct.id
WHERE pct.cargoid = package.id)) >= $GIVEN_MIN_FREIGHT)
AND (COALESCE(package.minfreight, (SELECT MIN(ct.minfreight)
FROM packagecartype AS pct
LEFT JOIN cartype AS ct
ON pct.cartypeid = ct.id
WHERE pct.cargoid = package.id)) <= $GIVEN_MAX_FREIGHT));
This works fine but it is way too slow. It times out if I use it on a DB with more than a few dozen entries which is just not acceptable. But sadly I am not that good at SQL and don't know how to improve this.
Any help is GREATLY appreciated!
July 26, 2018 at 9:43 am
Please provide ddl, sample data and expected output. Looking at your query, the coalesce function on the where clause isn't good
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 26, 2018 at 9:55 am
This is a SQL Server forum. You may have more success if you post your questions on a forum dedicated to Postgres.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 26, 2018 at 10:23 am
I am working with postgresql.
Example:
Package Table
id | minfreight | maxfreight
1 | 10 | 150
2 | null | null
3 | 300 | 400
Cartypes
id | minfreight | maxfreight | name
1 | 100 | 200 | 'small'
2 | 201 | 400 | 'large'
Packagecartype
id | packageid | cartypeid
1 | 2 | 1
Search input
minfreight: 10 , maxfreight: 150
Expected Output:
packages with id: 1, 2
Searchinput:
Cartype: small
Expected Output:
packages with id: 1, 2
Searchinput:
Cartype: large
Expected Output:
packages with id: 3
July 26, 2018 at 10:24 am
@phil
I didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!
July 26, 2018 at 10:37 am
a.sophiewirth - Thursday, July 26, 2018 10:24 AM@philI didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!
Sorry, but I do not.
Unfortunately, not all SQL dialects are the same. The Postgres SQL you posted, for example, does not even parse as valid SQL in SQL Server, making it difficult for people who do not know Postgres to help. That's one of the reasons why finding a dedicated forum is a good idea for you.
Having said that, I know that there are people here who know Postgres as well as SQL Server, so you may yet be in luck.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 26, 2018 at 10:54 am
here is what I think the ddl is that I asked you for, but your search criteria doesn't make any sense. can you elaborate? Also, Phil's been on this forum for a long time and has helped alot of people. I hope that was an honest response and not a smart-ass remark, which was how I interpreted it.
Drop table if exists #Package
Drop table if exists #CarType
Drop table if exists #PackageCarType
Create table #Package(PackageID int, MinFreight int, MaxFreight int)
insert into #Package
Values
(1,10,150),
(2,null,null),
(3,300,400)
Create table #CarType (CarTypeID int, MinFreight int, MaxFreight int, Name varchar(10))
insert into #CarType
Values
(1,100,200,'Small'),
(2,201,400,'Large')
Create table #PackageCarType (Id int, PackageID int, CarTypeID int)
insert into #PackageCarType values(1,2,1)
select p.MinFreight PackageMinFreight, p.MaxFreight PackageMaxFreight, pc.PackageID
,c.MinFreight CarMinFreight, c.MaxFreight CarMaxFreight, c.Name, c.CarTypeID
from #Package p
left join #PackageCarType pc
on p.PackageID = pc.PackageID
left join #CarType c
on c.CarTypeID = pc.CarTypeID
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 26, 2018 at 10:59 am
a.sophiewirth - Thursday, July 26, 2018 10:24 AM@philI didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!
I just did a search on this: Postgres forums
And there are at least a few with activity in July 2018. It took less than a minute to find these.
Sue
July 26, 2018 at 11:05 am
WITH MIN_MAX AS (
SELECT pct.cargoid AS PACKAGE_ID,
MIN(ct.minfreight) AS MIN_minfreight,
MAX(ct.maxfreight) AS MAX_maxfreight
FROM packagecartype AS pct
LEFT OUTER JOIN cartype AS ct
ON pct.cartypeid = ct.id
GROUP BY pct.cargoid
)
SELECT P.*
FROM package AS P
INNER JOIN MIN_MAX AS MM
ON P.id = MM.PACKAGE_ID
WHERE COALESCE(P.maxfreight, MM.MAX_maxfreight) >= $GIVEN_MIN_FREIGHT
AND COALESCE(P.minfreight, MM.MIN_minfreight) <= $GIVEN_MAX_FREIGHT;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 26, 2018 at 11:16 am
This solution is untested and might not even solve the problem. It's using what should be generic SQL (except for the variables).
The lenght of the code is not the reason for the timeout. The execution time (and configuration) is the reason for the timeout. Right now, you're reading 3 whole tables and that might be expensive. Additionally, you're reading 2 of them twice.
There might be a better option to better profit of indexes, but I'm not sure what's available in Postgres and what's not, nor I'm aware of how that engine works internally.
SELECT *
FROM package AS p
LEFT JOIN (SELECT pct.cargoid, MAX(ct.maxfreight) AS maxfreight, MIN(ct.minfreight) AS minfreight
FROM packagecartype AS pct
LEFT JOIN cartype AS ct ON pct.cartypeid = ct.id
GROUP BY pct.cargoid) AS pc ON pc.cargoid = package.id
WHERE (p.maxfreight >= $GIVEN_MIN_FREIGHT OR (p.maxfreight IS NULL AND pc.maxfreight >= $GIVEN_MIN_FREIGHT))
AND (p.minfreight <= $GIVEN_MAX_FREIGHT OR (p.minfreight IS NULL AND pc.minfreight <= $GIVEN_MAX_FREIGHT));
July 27, 2018 at 2:51 am
@phil:
Don't worry about it, posting here was a long shot, but let's hope I do get lucky! Thank you for the tips.
@mike-2: Not really sure in what way asking to follow up on a given tip is a 'smart ass' reply. I didn't see any active, psql specific forums so I asked to be linked to one, seeing as phil suggested I was coming to wrong place by posting here.
What about my search criteria doesn't make sense to you? I am glad to elaborate.
Thank you very much for taking the time to type out an answer, but I can absolutely not drop ANY tables, seeing as I am working on an active, LARGE production DB. I will keep this better way to structure things in mind for future needed relations of the sort though. I appreciate it!
@sue: I didn't. I found a few dead psql forums and the official postgresql community, where I can't post any psql questions as far as I can see though. So if you do have any links for me, I'd appreciate them.
@steve-2 and @luis: Thank you very much for your help, I'll try the code and come back to you! But I really appreciate you taking the time to type this out!
July 27, 2018 at 6:32 am
It was just the way I read it. I apologize and I'm glad it was me who misinterpreted it and not your intent. As far as dropping tables, those we just temp tables. On this forum, alot of us will use temp tables in the examples. They only last as along as the session is open and then are deleted automatically (plus we don't know the names of your tables, so we make examples). I wouldn't recommend dropping any 'real' tables either.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 27, 2018 at 6:50 am
@mike-2: That's alright, I suppose the internet has made us all a little more cynical.
Ah, I understand, thank you for explaining! Database structures and using them in bigger contexts is still extremely new to me so I understand basically nothing as of yet.
@steve-2: I have adapted your code and it seems to be working amazingly well, just the INNER JOIN had to become a LEFT JOIN, Thank you a lot for your help! I am very grateful for it, I was really stuck here.
@luis, Yeah I think my topic was worded badly, I understand it wasn't really the length of my code that caused the timeout. Your example has been really helpful in figuring out how I can chain JOINTS better together. Thank you!
July 27, 2018 at 7:17 am
a.sophiewirth - Thursday, July 26, 2018 10:24 AM@philI didn't find a proper, active postgresql forum, but if you've got one I'd appreciate a link!
dba.stackexchange.com would be my recommendation. Make sure to tag your question as PostgreSQL.
Here is a reddit discussion of the best PostgreSQL related forums:
July 27, 2018 at 7:58 am
@sestell: I did indeed post my question to stackexchange as well, though I did not receive as much help as here. To post on reddit never occurred to me though, so I will definitely keep that in mind! Thank you!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply