Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 31.07.2017 19:42, Jeff Janes wrote: > I think it is simply because no one has gotten around to implementing > it that way. When you can just write it as a values list instead, the > incentive to make the regular in-list work better is not all that strong. > > Cheers, > > Jeff I see from

Re: [GENERAL] Planner statistics usage for composite type

On 07/19/2017 06:37 PM, Tom Lane wrote: > Dmitry Lazurkin <dila...@gmail.com> writes: >> I am trying to find workaround for cross-column statistics. >> ... >> Worn estimate. Planner doesn't use statistics. In code I see usage of >> function scalargtsel which re

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 05:50, Jeff Janes wrote: It isn't either-or. It is the processing of millions of rows over the large in-list which is taking the time. Processing an in-list as a hash table would be great, but no one has gotten around to it implementing it yet. Maybe Dmitry will be the one to

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 00:17, PT wrote: The IN clause is not what's taking all the time. It's the processing of millions of rows that's taking all the time. IN (...) - 17 sec IN (VALUES ...) - 4 sec So performance issue is with IN-clause. Perhaps you should better describe what it is you really want

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 00:31, David G. Johnston wrote: Basically you want to write something like: SELECT * FROM ids JOIN ( :values_clause ) vc (vid) ON (vc.vid = ids.id )​ or WITH vc AS (SELECT vid FROM ORDER BY ... LIMIT ) SELECT * FROM ids JOIN vc ON (vid = ids.id

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 01:25, David G. Johnston wrote: On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin <dila...@gmail.com <mailto:dila...@gmail.com>>wrote: ALTER TABLE ids ALTER COLUMN id SET NOT NULL; EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN :v

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 01:15, David G. Johnston wrote: On Mon, Jul 24, 2017 at 3:12 PM, Dmitry Lazurkin <dila...@gmail.com <mailto:dila...@gmail.com>>wrote: And I have one question. I don't understand why IN-VALUES doesn't use Semi-Join? PostgreSQL has Hash Semi-Join... Fo

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 23.07.2017 14:35, dilaz03 . wrote: > - IN-VALUES clause adds new node to plan. Has additional node big > overhead? How about filter by two or more IN-VALUES clause? > Hmmm. This works. -- Full table can fit in memory show shared_buffers; shared_buffers 4GB show work_mem;

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 07/24/2017 01:40 AM, PT wrote: > In this example you count approximately 40,000,000 values, which is > about 40% of the table. 4 000 000 (: > If you really need these queries to be faster, I would suggest > materializing the data, i.e. create a table like: > > CREATE TABLE id_counts ( > id

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 07/24/2017 01:40 AM, PT wrote: > In this example you count approximately 40,000,000 values, which is > about 40% of the table. 4 000 000 (: > If you really need these queries to be faster, I would suggest > materializing the data, i.e. create a table like: > > CREATE TABLE id_counts ( > id

[GENERAL] Planner statistics usage for composite type

Hello. I am trying to find workaround for cross-column statistics. For example, I have tags with similarity: select version(); version

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 08/01/2017 07:13 PM, Jeff Janes wrote: > I think that HashSet is a Java-specific term. It is just a hash table > in which there is no data to store, just the key itself (and probably > a cash of the hashcode of that key), correct? Yes. And in Java HashSet implemented on top of HashMap (: >

[GENERAL] Change location of function/type installed from C-extension

Hello. I have database with installed pg_trgm extension with module path '/usr/lib/pg_trgm' (yes, this is mistake without $libdir (: ). Now I want upgrade postgresql to new major version. I keep old version 9.3 in /opt/postgresql/9.3 and new version 9.6 in /usr. Old version $libdir -

Re: [GENERAL] Change location of function/type installed from C-extension

I try investigate where PotsgreSQL keeps path of load libraries in catalog. select version(); version -- PostgreSQL 9.3.4 on

Re: [GENERAL] Change location of function/type installed from C-extension

On 26.08.2017 15:10, Dmitry Lazurkin wrote: > I try investigate where PotsgreSQL keeps path of load libraries in catalog. > > select version(); >

Re: [GENERAL] Change location of function/type installed from C-extension

On 26.08.2017 18:24, Tom Lane wrote: > You need to use pg_relation_filenode(): > > regression=# select relname, pg_relation_filenode(oid) from pg_class where > relname like 'pg_proc%'; > relname | pg_relation_filenode >

Re: [GENERAL] Change location of function/type installed from C-extension

On 26.08.2017 22:05, Tom Lane wrote: > Dmitry Lazurkin <dila...@gmail.com> writes: >> Thanks. Can I update "pg_proc.probin" without any problems? > Should work. I'd experiment in a scratch database before doing > it in production, but I c