Re: [GENERAL] Query Using Massive Temp Space

Hi, there are many Hash and Merge joins that may generate a lot of temp space, it could also be a problem of forgotten column in the join. Could you also provide indexes definitions (pk, uk and others) with the EXPLAIN (ANALYZE BUFFERS) (you can limit the scope of the query to help it to finish

Re: [GENERAL] Query Using Massive Temp Space

, November 20, 2017 7:17 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Query Using Massive Temp Space Hello, I have a query that is using a tremendous amount of temp disk space given the overall size of the dataset. I'd love for someone to try to explain what PG is doing and wh

Re: [GENERAL] Query Using Massive Temp Space

Cory Tucker wrote: > I have a query that is using a tremendous amount of temp disk space given the > overall size of the dataset. > I'd love for someone to try to explain what PG is doing and why its using so > much space for the query. It could be a sort or a hash operation. Do determine what

[GENERAL] Query Using Massive Temp Space

Hello, I have a query that is using a tremendous amount of temp disk space given the overall size of the dataset. I'd love for someone to try to explain what PG is doing and why its using so much space for the query. First off, the system is PG 9.6 on Ubuntu with 4 cores and 28 GB of RAM. The qu

Re: [GENERAL] Query on pg_settings view

Greetings, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > Ronen Nofar wrote: > > I have a weird case when running a query on the pg_settings view. > > I have two users, first one is the default user - postgres which is a > > superuser > > and another one is a role which i had created, i call

Re: [GENERAL] Query on pg_settings view

Ronen Nofar wrote: > I have a weird case when running a query on the pg_settings view. > I have two users, first one is the default user - postgres which is a > superuser > and another one is a role which i had created, i called it test_role and it's > not a superuser. > When I run a select on p

[GENERAL] Query on pg_settings view

Hi, I have a weird case when running a query on the pg_settings view. I have two users, first one is the default user - postgres which is a superuser and another one is a role which i had created, i called it test_role and it's not a superuser. When I run a select on pg_settings with these two us

[GENERAL] Query Improvement??

Goal is to return all vendors which exist in all three companies I think I got lucky figuring this out. Is there an obviously better way? combined_item_master looks like this: company_code character varying(10) NOT NULL, primary_vendor_no character varying(7) ..more fields data looks like this:

Re: [GENERAL] Query plan for Merge Semi Join

Peter J. Holzer wrote: > [PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by gcc (Debian > 4.9.2-10) 4.9.2, 64-bit] > > While investigating a performance issue, I found this query plan: > > wds=> explain analyze > select facttablename, columnname, term, concept_id, t.hidden, language, >

[GENERAL] Query plan for Merge Semi Join

[PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit] While investigating a performance issue, I found this query plan: wds=> explain analyze select facttablename, columnname, term, concept_id, t.hidden, language, register, c.id, canonicalname, descript

Re: [GENERAL] query not scaling

On 10/31/2017 03:12 AM, Laurenz Albe wrote: Rob Sargent wrote: I think your biggest problem is the join condition on m.basepos between s.startbase and s.endbase That forces a nested loop join, which cannot be performed efficiently. Agree! 800,000 * 4,000 = 3,200,000,000. It's just that I

Re: [GENERAL] query not scaling

On Thu, 2017-10-26 at 19:07 -0600, Rob Sargent wrote: >    ->  Nested Loop  (cost=3799.40..44686205.23 rows=1361304413 width=40) > (actual time=55.443..89684.451 rows=75577302 loops=1) >  ->  Hash Join  (cost=3798.98..43611.56 rows=823591 width=32) >

Re: [GENERAL] query not scaling

On Thu, Oct 26, 2017 at 10:01 AM, Tom Lane wrote: > Laurenz Albe writes: >> Also, to have PostgreSQL inline the function, which would be good >> for performance, it should be declared IMMUTABLE. > > Actually, if you hope to have a SQL function be inlined, it's better > not to decorate it at all -

Re: [GENERAL] query not scaling

On 10/26/2017 09:01 AM, Tom Lane wrote: Laurenz Albe writes: Also, to have PostgreSQL inline the function, which would be good for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it at all --- not with IMMUTABL

Re: [GENERAL] query not scaling

On 10/26/2017 09:01 AM, Tom Lane wrote: Laurenz Albe writes: Also, to have PostgreSQL inline the function, which would be good for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it at all --- not with IMMUTAB

Re: [GENERAL] query not scaling

Laurenz Albe writes: > Also, to have PostgreSQL inline the function, which would be good > for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it at all --- not with IMMUTABLE, and not with STRICT either. Both of

Re: [GENERAL] query not scaling

> On Oct 26, 2017, at 1:02 AM, Laurenz Albe wrote: > > Rob Sargent wrote: >> I have a query I cannot tame and I'm wondering if there's an alternative >> to the "between" clause I'm using. Perhaps a custom type could do >> better? I've tried the "<@" orperator and that changes the quer

Re: [GENERAL] query not scaling

Rob Sargent wrote: > I have a query I cannot tame and I'm wondering if there's an alternative > to the "between" clause I'm using. Perhaps a custom type could do > better? I've tried the "<@" orperator and that changes the query plan > significantly but the execution cost/time is not impr

[GENERAL] query not scaling

I have a query I cannot tame and I'm wondering if there's an alternative to the "between" clause I'm using. Perhaps a custom type could do better? I've tried the "<@" orperator and that changes the query plan significantly but the execution cost/time is not improved. Any suggestion or p

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

On 29 April 2017 at 11:37, David G. Johnston wrote: >> > Perhaps there are reasons why this optimization is not safe that I >> > haven't >> > thought about? >> >> Yeah, I think so. What happens if an A row cannot find a match in B or >> C? This version of the query will end up returning fewer rows

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

On Fri, Apr 28, 2017 at 3:24 PM, David Rowley wrote: > On 29 April 2017 at 07:59, Dave Vitek wrote: > > Is what I want in the query planner's vocabulary? It would need to > exploit > > the fact that the _id columns are not nullable, and either exploit the > > uniqueness of the id columns or do

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

On 29 April 2017 at 07:59, Dave Vitek wrote: > Is what I want in the query planner's vocabulary? It would need to exploit > the fact that the _id columns are not nullable, and either exploit the > uniqueness of the id columns or do an extra LIMIT step after the join. I > think I want it to effec

[GENERAL] query planner placement of sort/limit w.r.t. joins

Hi all, I have a query I'd like to speed up. I am wondering whether the query planner is capable of coming up with a certain kind of plan for this query, and if I might tickle it into doing so, or if I have to explicitly use subqueries to get what I want. Imagine we have tables A, B, and C

Re: [GENERAL] Query which "never" terminates

Hello, and thanks for your reply. SET from_collapse_limit=1; did no difference, I’m afraid. I ran both VACUUM and ANALYZE before I posted, and there was no noticable difference from either of them. Regards, /Viktor > On 27 apr 2017, at 15:54, pinker wrote: > > Try to change from_collaps

Re: [GENERAL] Query which "never" terminates

Try to change from_collapse_limit values at first, for instance run SET from_collapse_limit = 1; and then your query in the same session. have you ran analyze lately? -- View this message in context: http://www.postgresql-archive.org/Query-which-never-terminates-tp5958734p5958736.html Sent fr

[GENERAL] Query which "never" terminates

Dear sirs. I have an issue which I am at a loss to solve. Because of the behaviour I am not certain that this is a “slow query” problem, so I send my question here. My question regards a query using deep subqueries. EXPLAIN gives a maximum cost of about 2.000 (details below), but the query runs

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

Daniel Westermann writes: >> Thank you, Merlin. As said I know that "not in" is not a good choice in this >> case but I still do not get what is going here. Why does the >> repeatedly search for NULL values when I decrease work_mem and why not when >> increasing work_mem? >The core point is t

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

Daniel Westermann writes: > Thank you, Merlin. As said I know that "not in" is not a good choice in this > case but I still do not get what is going here. Why does the server > repeatedly search for NULL values when I decrease work_mem and why not when > increasing work_mem? The core point is

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

2017-04-05 10:33 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com > : 2017-04-05 10:13 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com > : BQ_BEGIN 2017-04-05 9:28 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com > : BQ_BEGIN >>what is r

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

2017-04-05 10:33 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com>: > 2017-04-05 10:13 GMT+02:00 Daniel Westermann services.com>: > >> 2017-04-05 9:28 GMT+02:00 Daniel Westermann > services.com>: >> >>> >>what is result of EXPLAIN statement for slow and fast cases? >>> >> >>> >>re

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

2017-04-05 10:13 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com > : 2017-04-05 9:28 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com > : BQ_BEGIN >>what is result of EXPLAIN statement for slow and fast cases? >> >>regards >> >>Pavel For work_mem=32MB

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

2017-04-05 10:13 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com>: > 2017-04-05 9:28 GMT+02:00 Daniel Westermann services.com>: > >> >>what is result of EXPLAIN statement for slow and fast cases? >> >> >> >>regards >> >> >> >>Pavel >> >> For work_mem=32MB >> >> explain (analyze,v

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

2017-04-05 9:28 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com > : >>what is result of EXPLAIN statement for slow and fast cases? >> >>regards >> >>Pavel For work_mem=32MB explain (analyze,verbose,buffers) select count(user_id) from users where user_id not in ( selec

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

2017-04-05 9:28 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com>: > >>what is result of EXPLAIN statement for slow and fast cases? > >> > >>regards > >> > >>Pavel > > For work_mem=32MB > > explain (analyze,verbose,buffers) select count(user_id) from users where > user_id not in (

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

>>what is result of EXPLAIN statement for slow and fast cases? >> >>regards >> >>Pavel For work_mem=32MB explain (analyze,verbose,buffers) select count(user_id) from users where user_id not in ( select id from ids); QUERY PLAN -

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

2017-04-05 8:57 GMT+02:00 Daniel Westermann < daniel.westerm...@dbi-services.com>: > >> I have set work_mem to a very low value intentionally for demonstration > >> purposes: > >> > >> postgres=# show work_mem; > >> work_mem > >> -- > >> 16MB > >> (1 row) > >> > >> postgres=# show shared

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

>> I have set work_mem to a very low value intentionally for demonstration >> purposes: >> >> postgres=# show work_mem; >> work_mem >> -- >> 16MB >> (1 row) >> >> postgres=# show shared_buffers ; >> shared_buffers >> >> 128MB >> (1 row) >> >> >> When I run t

Re: [GENERAL] Query never completes with low work_mem (at least not within one hour)

On Tue, Apr 4, 2017 at 8:20 AM, Daniel Westermann wrote: > Hi, > > PostgreSQL 9.6.2 on CentOS 7.3 x64. > > This is my data set: > > drop table if exists users; > drop table if exists ids; > create table users ( user_id int >, username varchar(50) >); > with

[GENERAL] Query never completes with low work_mem (at least not within one hour)

Hi, PostgreSQL 9.6.2 on CentOS 7.3 x64. This is my data set: drop table if exists users; drop table if exists ids; create table users ( user_id int , username varchar(50) ); with generator as ( select a.* from generate_series (1,300) a order by random() ) insert into users ( use

Re: [GENERAL] Query with type char

## Egon Frerich (e...@frerich.eu): > Why are there 0 rows? I expect 3 rows: >From your results I guess that sp3 IS NULL on the three rows where it is not 'j'. You should use the correct comparison predicate for this case, i.e. IS DISTINCT FROM instead of <>. https://www.postgresql.org/docs/9.6/st

[GENERAL] Query with type char

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Why are there 0 rows? I expect 3 rows: > mydb=# SELECT * FROM sample_char; > sp1 | sp2 | sp3 > -+-+- >1 | Bremen | >2 | Hamburg | >4 | Toronto | >3 | Bern| j >

Re: [GENERAL] Query with large in clauses uses a lot of memory

Wow. Thanks for the prompt answer. As a follow-up I was wondering if maybe there would be a way to tell it to NOT try to plan/execute the query (and instead throw an error) if the memory usage exceeded X. Thanks again. Greig -- View this message in context: http://postgresql.nabble.com/Quer

Re: [GENERAL] query locks up when run concurrently

Greetings! I tried running the application using the race detector but no data races were detected. To further narrow this down I removed the call to the function in question from the pool and instead run it one by one from a queue. This seems to have helped and the query didn't lock up anymore. I

Re: [GENERAL] Query regarding deadlock

On Thu, Nov 24, 2016 at 5:44 PM, Yogesh Sharma < yogesh1.sha...@nectechnologies.in> wrote: > Dear All, > > Thanks in advance. > I found below deadlock in postgresql logs. > I cannot change calling of REINDEX and insert query sequence because it is > execute automatically through some cron script.

Re: [GENERAL] query locks up when run concurrently

On 11/24/2016 02:14 PM, azhwkd wrote: Adrian Klaver mailto:adrian.kla...@aklaver.com>> schrieb am Do., 24. Nov. 2016 um 22:34 Uhr: On 11/24/2016 01:23 PM, azhwkd wrote: > It should not be possible because a group does not return to the > update pool before the update hasn't finished

Re: [GENERAL] Query regarding deadlock

Yogesh Sharma writes: > ERROR: deadlock detected > DETAIL: Process 2234 waits for AccessShareLock on relation 16459 of > database 16385; blocked by process 4111. > Process 4111 waits for ShareLock on relation 16502 of database 16385; > blocked by process 2234. > Process 2234: INSERT INTO ta

Re: [GENERAL] Query regarding deadlock

> If it is possible? > > Regards, > Yogesh > > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce > Sent: Friday, November 25, 2016 10:55 AM > To: pgsql-general@postgresql.org

Re: [GENERAL] Query regarding deadlock

On Fri, Nov 25, 2016 at 11:14 AM, Sameer Kumar wrote: > Does this mean that you reindex quite often based on a schedule. Personally I > don't prefer that. To me it is like you are trying to fix something that is > not broken. > > Ideally reindex only what needs to be reindexed. I would not want

Re: [GENERAL] Query regarding deadlock

. Regards, Yogesh -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Friday, November 25, 2016 10:55 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Query regarding deadlock On 11/24/2016 5:44

Re: [GENERAL] Query regarding deadlock

On Fri, 25 Nov 2016, 9:45 a.m. Yogesh Sharma, < yogesh1.sha...@nectechnologies.in> wrote: > Dear All, > > Thanks in advance. > I found below deadlock in postgresql logs. > I cannot change calling of REINDEX and insert query sequence because it is > execute automatically through some cron script. >

Re: [GENERAL] Query regarding deadlock

is possible? Regards, Yogesh -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Friday, November 25, 2016 10:55 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Query regarding deadlock On 11

Re: [GENERAL] Query regarding deadlock

On 11/24/2016 5:44 PM, Yogesh Sharma wrote: I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron script. any cron scripts are your own doing, so this statement makes no sense at all. -- john r pierce, recycling bits in santa cru

[GENERAL] Query regarding deadlock

Dear All, Thanks in advance. I found below deadlock in postgresql logs. I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron script. ERROR: deadlock detected DETAIL: Process 2234 waits for AccessShareLock on relation 16459 of datab

Re: [GENERAL] query locks up when run concurrently

On Thu, 2016-11-24 at 22:23 +0100, azhwkd wrote: > It should not be possible because a group does not return to the > update pool before the update hasn't finished. > I watched the queries in a postgres client and there was no overlap I > could see. > I don't really know what to make from this beh

Re: [GENERAL] query locks up when run concurrently

Adrian Klaver schrieb am Do., 24. Nov. 2016 um 22:34 Uhr: > On 11/24/2016 01:23 PM, azhwkd wrote: > > It should not be possible because a group does not return to the > > update pool before the update hasn't finished. > > So what is this 'update pool' and what is driving/using it? > > In other wo

Re: [GENERAL] query locks up when run concurrently

On 11/24/2016 01:23 PM, azhwkd wrote: It should not be possible because a group does not return to the update pool before the update hasn't finished. So what is this 'update pool' and what is driving/using it? In other words how is the determination of the parameters done? To be more specific

Re: [GENERAL] query locks up when run concurrently

It should not be possible because a group does not return to the update pool before the update hasn't finished. I watched the queries in a postgres client and there was no overlap I could see. I don't really know what to make from this behavior, sometimes when I start the application a few updates

Re: [GENERAL] query locks up when run concurrently

On 11/23/2016 10:41 PM, azhwkd wrote: The group ID is part of the primary key of the group_history table. My understanding is that two INSERTs with different group IDs should not collide in this case, or am I wrong in thinking this? After fresh coffee: In your first post you had this: "(In the

Re: [GENERAL] query locks up when run concurrently

On 11/23/2016 10:41 PM, azhwkd wrote: The group ID is part of the primary key of the group_history table. My understanding is that two INSERTs with different group IDs should not collide in this case, or am I wrong in thinking this? The table definition for group_history is the following: CREAT

Re: [GENERAL] query locks up when run concurrently

On 11/23/2016 10:41 PM, azhwkd wrote: The group ID is part of the primary key of the group_history table. My understanding is that two INSERTs with different group IDs should not collide in this case, or am I wrong in thinking this? The suspicion is that this: insert into group_history ("group

Re: [GENERAL] query locks up when run concurrently

On 11/23/2016 10:26 PM, azhwkd wrote: I'm sorry. I worded this quite poorly. I meant to say that there were no log lines added to the postgres logfile at the time. I hope these are the settings you were refferring to (I did not change anything in the config files vs. the default) log_destination

Re: [GENERAL] query locks up when run concurrently

The group ID is part of the primary key of the group_history table. My understanding is that two INSERTs with different group IDs should not collide in this case, or am I wrong in thinking this? The table definition for group_history is the following: CREATE TABLE public.group_history ( group int

Re: [GENERAL] query locks up when run concurrently

I'm sorry. I worded this quite poorly. I meant to say that there were no log lines added to the postgres logfile at the time. I hope these are the settings you were refferring to (I did not change anything in the config files vs. the default) log_destination = 'stderr' logging_collector = off log_

Re: [GENERAL] query locks up when run concurrently

On 11/23/2016 01:52 PM, azhwkd wrote: Greetings! The parallel calls should not be working on the same row. Each query services a different group ID on it's own and there is no overlap. Except the INSERT query in the trigger function is working on dates not group ids. Kind regards, Sebasti

Re: [GENERAL] query locks up when run concurrently

On 11/23/2016 01:47 PM, azhwkd wrote: Greetings! Yes I had a look at the logfiles but there is not a single logfile generated when I'm reproducing this issue. We are talking about the Postgres logfile, correct? I have to believe a log file exists, but maybe not entries at that time. What are

Re: [GENERAL] Query with large in clauses uses a lot of memory

greigwise writes: > So, I decided to try an experiment. I wrote 2 queries as follows: > 1 ) select pg_sleep(100) ; > 2 ) with q (s1, s2) as (select pg_sleep(100), 1) > select * from q where s2 in ( 1, delimited numbers>) > > It looks to me like the connection running the big query is usi

[GENERAL] Query with large in clauses uses a lot of memory

I had an issue today where the OOM killer terminated one of my postgres processes. On my server I have 8 GB of RAM, shared_memory is 1 GB and work_memory is 24MB. I have connection pooling which limits us to 25 connections. Even if I'm maxed out there, I'm still only using 1.6 MB of RAM of my 8

Re: [GENERAL] query locks up when run concurrently

Greetings! The parallel calls should not be working on the same row. Each query services a different group ID on it's own and there is no overlap. Kind regards, Sebastian Tom Lane schrieb am Mi., 23. Nov. 2016 um 17:47 Uhr: > azh...@gmail.com writes: > > I have a query which if run alone usua

Re: [GENERAL] query locks up when run concurrently

Greetings! Yes I had a look at the logfiles but there is not a single logfile generated when I'm reproducing this issue. Concerning locks I used there queries at https://wiki.postgresql.org/wiki/Lock_Monitoring and they came up empty. The group_history table and its sub-tables do not have any fore

[GENERAL] query locks up when run concurrently

Greetings! I'm using postgres version 9.5.5 on a ubuntu 16.04.1 server installation which was installed through apt-get. I have a query which if run alone usually completes in about 300ms. When run in my application this query constantly locks up and bogs down all connections of the connection po

Re: [GENERAL] query locks up when run concurrently

azh...@gmail.com writes: > I have a query which if run alone usually completes in about 300ms. > When run in my application this query constantly locks up and bogs > down all connections of the connection pool (In the application this > query is run up to 10 times in parallel with different paramet

Re: [GENERAL] query locks up when run concurrently

On 11/22/2016 11:41 PM, azh...@gmail.com wrote: Greetings! I'm using postgres version 9.5.5 on a ubuntu 16.04.1 server installation which was installed through apt-get. I have a query which if run alone usually completes in about 300ms. When run in my application this query constantly locks up

[GENERAL] query locks up when run concurrently

Greetings! I'm using postgres version 9.5.5 on a ubuntu 16.04.1 server installation which was installed through apt-get. I have a query which if run alone usually completes in about 300ms. When run in my application this query constantly locks up and bogs down all connections of the connection po

Re: [GENERAL] Query generator

2016-10-16 8:54 GMT-02:00 Andreas Seltenreich : > Vinicius Segalin writes: > > > 2016-09-29 16:32 GMT-03:00 Julien Rouhaud : > > > > > You should try sqlsmith (https://github.com/anse1/sqlsmith), which > works > > > very well. > > > > I had found this one before, but all I could get was queries

Re: [GENERAL] Query generator

Vinicius Segalin writes: > 2016-09-29 16:32 GMT-03:00 Julien Rouhaud : > > > You should try sqlsmith (https://github.com/anse1/sqlsmith), which works > > very well. > > I had found this one before, but all I could get was queries using > "standard" tables, like the ones from pg_catalog and > in

Re: [GENERAL] Query help

Try this: select distinct vendor_no, vendor_name from ap_vendors where vendor_no in ( select vendor_no from ap_vendors group by vendor_no having array_agg(company_code) @> ARRAY['BUR','EBC','SNJ']) On Wed, Oct 5, 2016 at 1:31 PM, Bret Stern wrote: > Good evening, > I'm curious about a

Re: [GENERAL] Query help

> On Oct 4, 2016, at 9:31 PM, Bret Stern > wrote: > > Good evening, > I'm curious about a way to ask the following question of my vendors > table. > > psuedo1 "select all vendors which exist in BUR and EBC and SNJ" > > and > psuedo2 "select all vendors which DO NOT exist in all three show roo

[GENERAL] Query help

Good evening, I'm curious about a way to ask the following question of my vendors table. psuedo1 "select all vendors which exist in BUR and EBC and SNJ" and psuedo2 "select all vendors which DO NOT exist in all three show rooms The data is from a Sage accounting system which I pull out and plac

Re: [GENERAL] Query killed with Out of memory

With a heavy query, when line number results raise over 600k query hangs with out of memory. Here is the explain analyze: [...] Work_mem is.512mb, shared buffers 3084mb and system Ram 10Gb. Postgres version is 8.4.8 and for some months i cannot upgrade. Is there a way to solve the problem? H

[GENERAL] Query killed with Out of memory

Hello, With a heavy query, when line number results raise over 600k query hangs with out of memory. Here is the explain analyze: CTE Scan on lista  (cost=25066.66..47721.23 rows=3678 width=260)    CTE lista ->  Unique  (cost=24956.32..25066.66 rows=3678 width=512)    ->  Sort  (

Re: [GENERAL] Query generator

On 29/09/2016 23:23, Vinicius Segalin wrote: > 2016-09-29 16:32 GMT-03:00 Julien Rouhaud >: > You should try sqlsmith (https://github.com/anse1/sqlsmith > ), which works > very well. > > > I had found this one befor

Re: [GENERAL] Query generator

2016-09-29 16:32 GMT-03:00 Julien Rouhaud : > On 29/09/2016 21:27, Vinicius Segalin wrote: > > Hi everyone, > > > > Hello, > > > Does anyone know a random query generator for Postgres? Something that > > gets my schema and, based on that, generates hundreds of different > > queries with join, grou

Re: [GENERAL] Query generator

On 29/09/2016 21:27, Vinicius Segalin wrote: > Hi everyone, > Hello, > Does anyone know a random query generator for Postgres? Something that > gets my schema and, based on that, generates hundreds of different > queries with join, group by, etc. > You should try sqlsmith (https://github.com/a

[GENERAL] Query generator

Hi everyone, Does anyone know a random query generator for Postgres? Something that gets my schema and, based on that, generates hundreds of different queries with join, group by, etc. Thank you.

[GENERAL] Query planner using hash join when merge join seems orders of magnitude faster

Hi all, I seem to be in a bit of a jam with the query planner choosing a significantly less optimal route for a query. First, a common check list I've seen around when researching these types of issues on list: 1. pgsql version is PostgreSQL 9.4.7 running on Amazon RDS 2. Vacuum analyze was run 3

Re: [GENERAL] Query from two tables return error

On Fri, Apr 1, 2016 at 4:04 PM, Adrian Klaver wrote: > On 04/01/2016 06:52 AM, arnaud gaboury wrote: > >> >> >> On Fri, Apr 1, 2016 at 3:33 PM, Sándor Daku > > > > >> One of many difficulties with computers that they do what you say >> them to do, not what you think or you think you are s

Re: [GENERAL] Query from two tables return error

On 04/01/2016 06:52 AM, arnaud gaboury wrote: On Fri, Apr 1, 2016 at 3:33 PM, Sándor Daku One of many difficulties with computers that they do what you say them to do, not what you think or you think you are saying. :) Lets see: SELECT d.home_dir FROM email.mail_dir d,

Re: [GENERAL] Query from two tables return error

On Fri, Apr 1, 2016 at 3:33 PM, Sándor Daku wrote: > > On 1 April 2016 at 11:33, arnaud gaboury wrote: > >> I have one table email.mail.mailusers : >> >>Table "email.mailusers" >>Column| Type | >> Modifiers

Re: [GENERAL] Query from two tables return error

On 1 April 2016 at 11:33, arnaud gaboury wrote: > I have one table email.mail.mailusers : > >Table "email.mailusers" >Column| Type | > Modifiers | Storage > | Stats target | Description > >

Re: [GENERAL] Query from two tables return error

On Fri, Apr 1, 2016 at 11:33 AM, arnaud gaboury wrote: > I have one table email.mail.mailusers : > >Table "email.mailusers" >Column| Type | > Modifiers | Storage > | Stats target | Description > > -

[GENERAL] Query from two tables return error

I have one table email.mail.mailusers : Table "email.mailusers" Column| Type | Modifiers | Storage | Stats target | Description -+--+

[GENERAL] query reboot pgsql 9.5.1

Hi!!! I try to explain my problem...sorry for my english :( In pgsql 9.5.1 I have a two tables with the next structure: 1. Tabla unlogged «public._gc_cat»    Columna | Tipo | Modificadores     -+--+---    idppicat| integer  | 

Re: [GENERAL] query reboot pgsql 9.5.1

David G. Johnston wrote: > ​To be more clear, you need to run a query that will complete in our > lifetime (and without an OOM error) with all three of ANALYZE, BUFFERS, and > TIMING specified for the EXPLAIN. I think the problem is pretty clear. The plan is sensible yet the result doesn't seem

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 3:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > >> 2016-03-04 15:58 GMT-06:00 David G. Johnston >> : >> >>> On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < >>> fjmolinabr...@gmail.com> wrote: >>> when i run without the ANALIZE the outpu

Re: [GENERAL] query reboot pgsql 9.5.1

> > > 2016-03-04 15:58 GMT-06:00 David G. Johnston : > >> On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < >> fjmolinabr...@gmail.com> wrote: >> >>> when i run without the ANALIZE the output is: >>> >>> pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a >>> LEFT jo

Re: [GENERAL] query reboot pgsql 9.5.1

No, these messages are not confuse; I just wanted to show output 2016-03-04 15:58 GMT-06:00 David G. Johnston : > On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < > fjmolinabr...@gmail.com> wrote: > >> when i run without the ANALIZE the output is: >> >> pba=# EXPLAIN ( TIMING, BUFFE

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > when i run without the ANALIZE the output is: > > pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a > LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama ); > ERROR

Re: [GENERAL] query reboot pgsql 9.5.1

when i run without the ANALIZE the output is: pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama ); ERROR: la opción BUFFERS de EXPLAIN requiere ANALYZE pba=# EXPLAIN ( TIMING) SELECT idprodxintegrar FROM _gc_t

Re: [GENERAL] query reboot pgsql 9.5.1

Felipe de Jesús Molina Bravo wrote: > pba=# \i tablas.sql > DROP TABLE > SELECT 120130 > CREATE INDEX > CREATE INDEX > CREATE INDEX > DROP TABLE > SELECT 91932 > CREATE INDEX > CREATE INDEX > pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb > a LEFT join _gc_cat b on (

Re: [GENERAL] query reboot pgsql 9.5.1

sorry...i made a mistake...my tables are unlogged and in the last test these was wiped... :( when i create the tables again (with all record) the result is: pba=# \i tablas.sql DROP TABLE SELECT 120130 CREATE INDEX CREATE INDEX CREATE INDEX DROP TABLE SELECT 91932 CREATE INDEX CREATE INDEX pba=#

  1   2   3   4   5   6   7   8   9   10   >