Sort question - Fractions, Metric etc
I have a table with metric, imperial, fraction columns. Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125) Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence. Just curious what the pro's do Bret
Re: Sort question - Fractions, Metric etc
On 8/14/21 9:14 AM, Bret Stern wrote: I have a table with metric, imperial, fraction columns. Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125) Alright how is this different from metric or fraction? I can sort of see fraction if you mean as 1/3, 1/20, etc. What is the data type of the field you are storing this in? Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence. Just curious what the pro's do Bret -- Adrian Klaver adrian.kla...@aklaver.com
Join for the parent table will not leverage the index scan
We have two tables, both have ~36 partitions. This is my first query, which attempts to join the parent tables on the columns that have been used to create multi-column indexes for all partitions (multi-column indexes defined on session_id, detail_id in that order): *PostgreSQL version : 10*-- *query* explain select * from dm_ci360.page_details pd inner join dm_ci360.page_details_ext pde on (pd.session_id = pde.session_id and pd.detail_id = pde.detail_id) where pd.session_dt >= now()::date-2; -- *plan* Hash Join (cost=36927955.75..100106605.07 rows=11 width=8355) Hash Cond: (((pd.session_id)::text = (pde.session_id)::text) AND ((pd.detail_id)::text = (pde.detail_id)::text)) -> Append (cost=1297.33..571870.20 rows=630535 width=8130) -> Bitmap Heap Scan on page_details_null pd (cost=1297.33..240207.85 rows=69259 width=8220) Recheck Cond: (session_dt >= ((now())::date - 2)) -> Bitmap Index Scan on page_details_null_session_dt_idx (cost=0.00..1280.02 rows=69259 width=0) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m3_session_dt_idx on page_details_m3 pd_1 (cost=0.44..4.71 rows=1 width=8216) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m2_session_dt_idx on page_details_m2 pd_2 (cost=0.43..102.79 rows=621 width=8253) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m1_session_dt_idx on page_details_m1 pd_3 (cost=0.44..4.55 rows=1 width=8281) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_5_session_dt_idx on page_details_5 pd_4 (cost=0.57..51622.53 rows=196574 width=8202) Index Cond: (session_dt >= ((now())::date - 2)) -> Bitmap Heap Scan on page_details_31 pd_5 (cost=982.61..194857.98 rows=52391 width=7721) Recheck Cond: (session_dt >= ((now())::date - 2)) -> Bitmap Index Scan on page_details_31_session_dt_idx1 (cost=0.00..969.51 rows=52391 width=0) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_49_session_dt_idx on page_details_49 pd_6 (cost=0.45..10991.08 rows=50039 width=7721) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_52_session_dt_idx on page_details_52 pd_7 (cost=0.44..2337.94 rows=10222 width=8297) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_59_session_dt_idx on page_details_59 pd_8 (cost=0.57..24790.17 rows=50148 width=8276) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_60_session_dt_idx on page_details_60 pd_9 (cost=0.44..7292.41 rows=4259 width=8263) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_61_session_dt_idx on page_details_61 pd_10 (cost=0.43..309.10 rows=704 width=8182) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_63_session_dt_idx on page_details_63 pd_11 (cost=0.43..13.20 rows=25 width=8250) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_64_session_dt_idx on page_details_64 pd_12 (cost=0.44..5.41 rows=1 width=8314) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_66_session_dt_idx on page_details_66 pd_13 (cost=0.42..4.49 rows=1 width=8277) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_67_session_dt_idx on page_details_67 pd_14 (cost=0.42..58.62 rows=325 width=8227) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_68_session_dt_idx on page_details_68 pd_15 (cost=0.42..35.20 rows=111 width=8259) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_78_session_dt_idx on page_details_78 pd_16 (cost=0.44..1738.10 rows=4608 width=8263) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_98_session_dt_idx on page_details_98 pd_17 (cost=0.44..2157.31 rows=7136 width=8225) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_101_session_dt_idx on page_details_101 pd_18 (cost=0.44..2956.03 rows=9241 width=8205) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_130_session_dt_idx on page_details_130 pd_19 (cost=0.44..1683.53 rows=19656 width=8199) Index Cond: (session_dt >= ((now())::date - 2)) -> Index Scan using page_details_m100_session_dt_idx on page_details_m100 pd_20 (cost=0.43..4.96 rows=1 width
Re: Sort question - Fractions, Metric etc
Strings; I haven't explored doing this with numeric types, but some columns needed alpha chars eg 13mm. Although I could have front ended this UI with mm nomenclature I did not. I'll put a table together with appropriate numeric types and see if the sort will behave. On 8/14/2021 9:21 AM, Adrian Klaver wrote: On 8/14/21 9:14 AM, Bret Stern wrote: I have a table with metric, imperial, fraction columns. Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125) Alright how is this different from metric or fraction? I can sort of see fraction if you mean as 1/3, 1/20, etc. What is the data type of the field you are storing this in? Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence. Just curious what the pro's do Bret
Re: Sort question - Fractions, Metric etc
On 8/14/21 9:37 AM, Bret Stern wrote: Strings; I haven't explored doing this with numeric types, but some columns needed alpha chars eg 13mm. Two columns: data_val(numeric) data_unit(varchar) 13 mm Although I could have front ended this UI with mm nomenclature I did not. I'll put a table together with appropriate numeric types and see if the sort will behave. It will. On 8/14/2021 9:21 AM, Adrian Klaver wrote: On 8/14/21 9:14 AM, Bret Stern wrote: I have a table with metric, imperial, fraction columns. Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125) Alright how is this different from metric or fraction? I can sort of see fraction if you mean as 1/3, 1/20, etc. What is the data type of the field you are storing this in? Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence. Just curious what the pro's do Bret -- Adrian Klaver adrian.kla...@aklaver.com
Re: Sort question - Fractions, Metric etc
I like that idea On 8/14/2021 9:46 AM, Adrian Klaver wrote: On 8/14/21 9:37 AM, Bret Stern wrote: Strings; I haven't explored doing this with numeric types, but some columns needed alpha chars eg 13mm. Two columns: data_val(numeric) data_unit(varchar) 13 mm Although I could have front ended this UI with mm nomenclature I did not. I'll put a table together with appropriate numeric types and see if the sort will behave. It will. On 8/14/2021 9:21 AM, Adrian Klaver wrote: On 8/14/21 9:14 AM, Bret Stern wrote: I have a table with metric, imperial, fraction columns. Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125) Alright how is this different from metric or fraction? I can sort of see fraction if you mean as 1/3, 1/20, etc. What is the data type of the field you are storing this in? Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence. Just curious what the pro's do Bret
Re: Sort question - Fractions, Metric etc
Adrian Klaver writes: > On 8/14/21 9:37 AM, Bret Stern wrote: >> I haven't explored doing this with numeric types, but some columns >> needed alpha chars eg 13mm. > Two columns: > data_val(numeric) data_unit(varchar) > 13mm It sounds like your data is a completely disorganized mess :-(. You might be able to bring some semblance of coherence to it with an extension like postgresql-unit [1], but it will take a lot of effort to get the data into a representation that can sort reasonably. There's no "easy button" here. regards, tom lane [1] https://github.com/df7cb/postgresql-unit
Re: Sort question - Fractions, Metric etc
On Sat, 14 Aug 2021 at 19:03, Tom Lane wrote: > Adrian Klaver writes: > > On 8/14/21 9:37 AM, Bret Stern wrote: > >> I haven't explored doing this with numeric types, but some columns > >> needed alpha chars eg 13mm. > > > Two columns: > > > data_val(numeric) data_unit(varchar) > > 13mm > > It sounds like your data is a completely disorganized mess :-(. > You might be able to bring some semblance of coherence to it with > an extension like postgresql-unit [1], but it will take a lot of > effort to get the data into a representation that can sort reasonably. > There's no "easy button" here. > > regards, tom lane > > [1] https://github.com/df7cb/postgresql-unit > > I'm not sure, but maybe a kind of cast function that cast everything into a reasonable common unit(Khm... millimeters) and sort on that? It seems relatively simple to pick up the value and unit from a string with a regexp. Admittedly the data would still be a mess. Regards, Sándor
Re: Sort question - Fractions, Metric etc
On 8/14/21 10:19 AM, Sándor Daku wrote: I'm not sure, but maybe a kind of cast function that cast everything into a reasonable common unit(Khm... millimeters) and sort on that? It seems relatively simple to pick up the value and unit from a string with a regexp. Admittedly the data would still be a mess. I see the potential for another crash landing:) : https://en.wikipedia.org/wiki/Mars_Climate_Orbiter#Cause_of_failure Regards, Sándor -- Adrian Klaver adrian.kla...@aklaver.com
Re: Sort question - Fractions, Metric etc
Of course it's a complete disorganized mess..That'a the world I live in.Best thing is, I'm walking away with some new strategies.You guys rock Original message From: Sándor Daku Date: 8/14/21 10:19 AM (GMT-08:00) To: Tom Lane Cc: Adrian Klaver , Bret Stern , pgsql-general@lists.postgresql.org Subject: Re: Sort question - Fractions, Metric etc On Sat, 14 Aug 2021 at 19:03, Tom Lane wrote:Adrian Klaver writes: > On 8/14/21 9:37 AM, Bret Stern wrote: >> I haven't explored doing this with numeric types, but some columns >> needed alpha chars eg 13mm. > Two columns: > data_val(numeric) data_unit(varchar) > 13 mm It sounds like your data is a completely disorganized mess :-(. You might be able to bring some semblance of coherence to it with an extension like postgresql-unit [1], but it will take a lot of effort to get the data into a representation that can sort reasonably. There's no "easy button" here. regards, tom lane [1] https://github.com/df7cb/postgresql-unitI'm not sure, but maybe a kind of cast function that cast everything into a reasonable common unit(Khm... millimeters) and sort on that? It seems relatively simple to pick up the value and unit from a string with a regexp. Admittedly the data would still be a mess. Regards,Sándor
Understaning postgresql instance vs database
In Informix, the instance is everything. Buffer pools, transaction logging, backup and restores and all monitoring is done at the instance level databases are just logical collections of tables in an instance. In DB2 there is some instance configuration, but most everything is at the database level. Buffer pools, logging, backups and restores are done at the database level. Process load monitoring might be done at the instance level with db2top. It seems like Postgres is like DB2, there are some things at the instance level and some at the database level. But I'm having a hard time sorting out what is where. Like what in PostgreSQL's statistics collector is an instance statistic verses a database statistic. I found a pgtop utility but it seems to require a database parameter So are all stats database specific?or is the top info instance stats and the list portion database specific?
Re: Understaning postgresql instance vs database
On 8/14/21 10:34 AM, Open _ wrote: Like what in PostgreSQL's statistics collector is an instance statistic verses a database statistic. I found a pgtop utility but it seems to require a database parameter So are all stats database specific? or is the top info instance stats and the list portion database specific? You probably want to start by looking at this page: https://www.postgresql.org/docs/current/monitoring-stats.html -- Adrian Klaver adrian.kla...@aklaver.com
Re: Sort question - Fractions, Metric etc
I would write a stable function converting everything to metric (or imperial, depends on your preferences) and sort on the return of the function. Since unit conversion functions do not need to modify the database and should always return the same values for the same arguments, the function can be used within a query (that is the meaning of the keyword "STABLE") and you can use it for sorting stuff Regards On 8/14/2021 12:14 PM, Bret Stern wrote: I have a table with metric, imperial, fraction columns. Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125) Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence. Just curious what the pro's do Bret -- Mladen Gogala Oracle DBA Tel: (347) 321-1217 Blog: https://dbwhisperer.wordpress.com
Re: Sort question - Fractions, Metric etc
You might define new types for temperature, length, whatever, with suitable conversion, operation and creation functions. You’d be able to define how the new types participate in indexes, support directly sorting on them, so you package up this complexity and forget about it. Either normalize everything to metric on creation, or have an enumeration or boolean flag to indicate whether the value is metric or imperial (choose this one if you want values to remember how they were created and thus how they will display, and to avoid rounding errors converting back to imperial for display). Depends how many places you use them whether this is worth it. But it would be a good way to make this complexity idiot-proof if you’ll be using it all over. You’d be able to just add and multiply lengths and such without worrying how they were specified. Looks like this might do what you need on cursory examination: https://github.com/df7cb/postgresql-unit On Aug 14, 2021, 12:51 -0700, Gogala, Mladen , wrote: > I would write a stable function converting everything to metric (or > imperial, depends on your preferences) and sort on the return of the > function. Since unit conversion functions do not need to modify the > database and should always return the same values for the same > arguments, the function can be used within a query (that is the meaning > of the keyword "STABLE") and you can use it for sorting stuff > > Regards > > On 8/14/2021 12:14 PM, Bret Stern wrote: > > I have a table with metric, imperial, fraction columns. > > > > Is there a way to sort correctly using imperial (eg; .125, .375, .437 > > -> 1., 1.125) > > > > Couldn't handle it with ORDER BY ASC, DESC args so I added a > > sort_column and sorted based > > > > on those values eg; 1,2,3,4,5,6 indicating the value I need to show in > > which sequence. > > > > > > Just curious what the pro's do > > > > Bret > > > > > -- > Mladen Gogala > Oracle DBA > Tel: (347) 321-1217 > Blog: https://dbwhisperer.wordpress.com > >
Re: Sort question - Fractions, Metric etc
On 8/14/21 1:24 PM, Bret Stern wrote: Here's the clip of the UI. The user selects whatever value the vendor provides for unit thickness. The data entry So the vendors supply the measurements in all the various units for a given item? people aren't comfortable converting. At this point the sort_order column managed to do the trickbut going forward on my next application, I can see a different approach. Are you suggesting a function like this shellsort used with an array. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Sort question - Fractions, Metric etc
Yep, I provide the UI for the user to select the vendor supplied unit value from the list. I don't want anyone entering (manually) any value, they must select from my list (fed from a PG table). (and since some units don't translate exactly, they pick the one that is closest). It's Tile (floor, shower etc), not the tightest tolerance of dimensions, so it's not catastrophic if it's off a 32nd or so. cheers On 8/14/2021 1:43 PM, Adrian Klaver wrote: On 8/14/21 1:24 PM, Bret Stern wrote: Here's the clip of the UI. The user selects whatever value the vendor provides for unit thickness. The data entry So the vendors supply the measurements in all the various units for a given item? people aren't comfortable converting. At this point the sort_order column managed to do the trickbut going forward on my next application, I can see a different approach. Are you suggesting a function like this shellsort used with an array.
Re: Sort question - Fractions, Metric etc
On 8/14/21 2:04 PM, Bret Stern wrote: Yep, I provide the UI for the user to select the vendor supplied unit value from the list. I should have been more specific, does the vendor do all the conversions and supply them to you? I don't want anyone entering (manually) any value, they must select from my list (fed from a PG table). (and since some units don't translate exactly, they pick the one that is closest). Yes, nominal dimensions. That is what I'm trying to work out, is this something that makes sense to the vendor and should not be tampered with? It's Tile (floor, shower etc), not the tightest tolerance of dimensions, so it's not catastrophic if it's off Is it only tile you are working with? a 32nd or so. cheers -- Adrian Klaver adrian.kla...@aklaver.com
Re: Sort question - Fractions, Metric etc
On 8/14/2021 2:13 PM, Adrian Klaver wrote: On 8/14/21 2:04 PM, Bret Stern wrote: Yep, I provide the UI for the user to select the vendor supplied unit value from the list. I should have been more specific, does the vendor do all the conversions and supply them to you? Vendors supply dimensions, depending on where in the world the product comes from, could be metric, imperial or fraction I don't want anyone entering (manually) any value, they must select from my list (fed from a PG table). (and since some units don't translate exactly, they pick the one that is closest). Yes, nominal dimensions. That is what I'm trying to work out, is this something that makes sense to the vendor and should not be tampered with? The vendor is out of the picture at this point. They provide a catalog, and we enter (or import) items into our POS system. We match their dimension within tolerance. It's Tile (floor, shower etc), not the tightest tolerance of dimensions, so it's not catastrophic if it's off Is it only tile you are working with? No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks and delivered in blocks of ten slabs normally, where tolerances are also +/- .032 I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speak in these terms, and don't publish to us, so we do the best we can. Getting vendors to supply the basic values is a struggle. a 32nd or so. cheers
Re: Sort question - Fractions, Metric etc
On 8/14/21 2:47 PM, Bret Stern wrote: On 8/14/2021 2:13 PM, Adrian Klaver wrote: On 8/14/21 2:04 PM, Bret Stern wrote: Vendors supply dimensions, depending on where in the world the product comes from, could be metric, imperial or fraction Yes, nominal dimensions. That is what I'm trying to work out, is this something that makes sense to the vendor and should not be tampered with? The vendor is out of the picture at this point. They provide a catalog, and we enter (or import) items into our POS system. We match their dimension within tolerance. It would seem then a entry system that specifies the initial input unit(metric, imperial) and then does the conversion to a set unit(my preference would be metric) for storage to the table in that unit. Then you would convert on the fly to fill out the display field. Or you could use something like generated columns(https://www.postgresql.org/docs/12/ddl-generated-columns.html) available in Postgres 12+ to pre-fill columns to save the overhead on subsequent queries. For pre-12 maybe a trigger on the table column to fill in the other columns. In any case you sort(order by) by your canonical column which would be of numeric type. No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks and delivered in blocks of ten slabs normally, where tolerances are also +/- .032 I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speak in these terms, and don't publish to us, so we do the best we can. Getting vendors to supply the basic values is a struggle. a 32nd or so. cheers -- Adrian Klaver adrian.kla...@aklaver.com
Re: Sort question - Fractions, Metric etc
On 15 Aug 2021, at 7:47, Bret Stern wrote: > I will say this business has been behind in the attributes game. Plus there > are many "artsy" vendors who can hardly speak in these terms, and don't > publish to us, so we do the best we can. > > Getting vendors to supply the basic values is a struggle. > I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and how much (mostly in units of dollar) 😉 After that there may be length width and thickness values specified with (hopefully) the same units ± weight (per unit or box?) ± allowance for grout/joining (in case your application is going to be used as an aid in estimating quantities needed) The truly artistic supplier will refrain from making anything the same and your customer will be expected to buy the item first and plan their project around it. One possible data wrangling scheme would be to give each item a catalogue number (yours) — Just noticed Adrain saying something similar so apologies for the overlap. The vendor_spec table would capture the vendor’s identifier, description and supplied dimensions (with a units column). The dimensions_view (suggest a materialised view) would carry the dimension information in converted form, e.g., mm: numeric(8,3) Once the conversions are setup the customer can be supplied with dimensions in the system of their choice and you have a sensible common point reference for any rankings. When I first saw this thread I thought you were getting into metal dimensions and started to wonder if your next phase was to “organise” bolts according to diameter length and thread… you have picked the easier course, the other is a nightmare, e.g., https://www.americanmachinetools.com/machinist_tables.htm Gavan Schneider —— Gavan Schneider, Sodwalls, NSW, Australia Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong. — H. L. Mencken, 1920
Re: Sort question - Fractions, Metric etc
On 8/14/21 4:05 PM, Gavan Schneider wrote: On 15 Aug 2021, at 7:47, Bret Stern wrote: I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speak in these terms, and don't publish to us, so we do the best we can. Getting vendors to supply the basic values is a struggle. I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and how much (mostly in units of dollar) 😉 Hmm, you are lucky:) Of late I get 'who knows' for how many and 'we'll see' for how much. Gavan Schneider —— Gavan Schneider, Sodwalls, NSW, Australia Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong. — H. L. Mencken, 1920 -- Adrian Klaver adrian.kla...@aklaver.com
