Re: Multi-column index: Which column order
On Tue, 2023-02-14 at 17:53 +, Sebastien Flaesch wrote: > When creating an index on multiple columns, does the order of the columns > matter? > (I guess so) It does, but not in your case. > It's mostly for SELECT statements using a condition that include ALL columns > of the index (pkey): > > SELECT * FROM art WHERE etb='L1' and code='ART345' > > I would naturally put the columns with the most various values first, and > > For example, if the "code" column contains thousands of various item ids like > 'SXZ874', > 'ERF345', ... while the "etb" column contains a dozen of values like "L1", > "LT" and "BX". > > Which one is best? > > CREATE UNIQUE INDEX ix1 ON art (code, etb) > or > CREATE UNIQUE INDEX ix1 ON art (etb, code) > > (or its PRIMARY KEY equivalent) Both are the same. There is an old myth that says that you should use the moew selective column first (which would be "code"), but that is just a myth. Order makes a difference for queries like "WHERE etb = 'L1' and code LIKE 'ART345'" or "WHERE code = 'ART345' ORDER BY etb", but not for two equality comparisons. > Does it depend on the type of index (Btree, GiST, etc) ? Yes, it does. For B-tree and GiST indexes order matters, for hash, GIN and BRIN indexes it doesn't (for various reasons). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
unresolved external symbol when building on Windows
I got a bunch of errors like below when building from source on Windows. My python version is 3.11. No change if I switch to 3.9. Would appreciate any pointer. -- Andy "C:\Users\test\source\repos\postgresql\pgsql.sln" (default target) (1) -> "C:\Users\test\source\repos\postgresql\plpython3.vcxproj" (default target) (74) ->(Link target) -> plpy_cursorobject.obj : error LNK2019: unresolved external symbol __imp__PyExc_StopIteration referenced in function _PLy_cursor_plan [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] ... plpy_util.obj : error LNK2019: unresolved external symbol __imp__PyUnicode_FromStringAndSize referenced in function _PLyUnicode_FromString [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] plpy_util.obj : error LNK2019: unresolved external symbol __imp__PyUnicode_FromString referenced in function _PLyUnicode_FromString [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] plpy_util.obj : error LNK2019: unresolved external symbol __imp__PyUnicode_AsUTF8String referenced in function _PLyUnicode_Bytes [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] .\Release\plpython3\plpython3.dll : fatal error LNK1120: 76 unresolved externals [C:\Users\test\source\repos\postgresql\plpython3.vcxproj]
Re: unresolved external symbol when building on Windows
Problem resolved. It is due to library machine type conflict: x64 vs x86. On Wed, Feb 15, 2023 at 6:00 PM King of Hearts wrote: > I got a bunch of errors like below when building from source on Windows. > My python version is 3.11. No change if I switch to 3.9. Would > appreciate any pointer. -- Andy > > "C:\Users\test\source\repos\postgresql\pgsql.sln" (default target) (1) -> > "C:\Users\test\source\repos\postgresql\plpython3.vcxproj" (default target) > (74) ->(Link target) -> > plpy_cursorobject.obj : error LNK2019: unresolved external symbol > __imp__PyExc_StopIteration referenced in function _PLy_cursor_plan > [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] > ... > plpy_util.obj : error LNK2019: unresolved external symbol > __imp__PyUnicode_FromStringAndSize referenced in function > _PLyUnicode_FromString > [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] > plpy_util.obj : error LNK2019: unresolved external symbol > __imp__PyUnicode_FromString referenced in function _PLyUnicode_FromString > [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] > plpy_util.obj : error LNK2019: unresolved external symbol > __imp__PyUnicode_AsUTF8String referenced in function _PLyUnicode_Bytes > [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] > .\Release\plpython3\plpython3.dll : fatal error LNK1120: 76 unresolved > externals [C:\Users\test\source\repos\postgresql\plpython3.vcxproj] >
Re: Query plan for "id IS NULL" on PK
On 2/14/23 18:21, David Rowley wrote: [snip] since it likely only applies to nearly zero real-world cases Are you sure? -- Born in Arizona, moved to Babylonia.
Re: Multi-column index: Which column order
On 2/15/23 02:46, Laurenz Albe wrote: [snip] Which one is best? CREATE UNIQUE INDEX ix1 ON art (code, etb) or CREATE UNIQUE INDEX ix1 ON art (etb, code) (or its PRIMARY KEY equivalent) Both are the same. There is an old myth that says that you should use the moew selective column first (which would be "code"), but that is just a myth. Only on Postgresql? -- Born in Arizona, moved to Babylonia.
Re: Multi-column index: Which column order
On Wed, 2023-02-15 at 10:20 -0600, Ron wrote: > On 2/15/23 02:46, Laurenz Albe wrote: > > Which one is best? > > > CREATE UNIQUE INDEX ix1 ON art (code, etb) > > > or > > > CREATE UNIQUE INDEX ix1 ON art (etb, code) > > > > > > (or its PRIMARY KEY equivalent) > > Both are the same. > > > > There is an old myth that says that you should use the more selective > > column first > > (which would be "code"), but that is just a myth. > > Only on Postgresql? No, on all relational databases that use B-tree indexes. Look at how many index entries have to be scanned in both cases, and you will see. Yours, Laurenz Albe
Re: Multi-column index: Which column order
On 2/15/23 21:45, Laurenz Albe wrote: On Wed, 2023-02-15 at 10:20 -0600, Ron wrote: On 2/15/23 02:46, Laurenz Albe wrote: [snip] Both are the same. There is an old myth that says that you should use the more selective column first (which would be "code"), but that is just a myth. Only on Postgresql? No, on all relational databases that use B-tree indexes. Not only is "all" is a very absolute word (I know of a counter-example), but querying on the second segment means that you have to scan the whole tree instead of isolating one sub-branch. -- Born in Arizona, moved to Babylonia.
