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

Hello, I have one question… Why you using so huge amount of grouping columns? Is there some reason for it? It is not definitelly fast method. I would prefer firstly do named query grouped by ids (account_id, candidate_id, parent_id) and then left join table candidates (to get rest of requested

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

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

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, >

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

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

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

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

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

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

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

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

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

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

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

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

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 > > -

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=#

Re: [GENERAL] query reboot pgsql 9.5.1

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

Re: [GENERAL] query reboot pgsql 9.5.1

the output is: pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama ) ; QUERY PLAN --

Re: [GENERAL] query reboot pgsql 9.5.1

the result was the same: *pba=# ANALYZE VERBOSE public._gc_cat;INFO: analizando «public._gc_cat»INFO: «_gc_cat»: se procesaron 1999 de 1999 páginas, que contenían 91932 filas vigentes y 0 filas no vigentes; 3 filas en la muestra, 91932 total de filas estimadasANALYZEpba=# ANALYZE

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 1:52 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > > >>> Now i execute the same in pgsql 9.4.5 and all is fine!!! >>> >>> The EXPLAINs are: >>> >>> - pgsql 9.5.1: >>> >>>Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) >>> ->

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 3:52 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > > >>> Now i execute the same in pgsql 9.4.5 and all is fine!!! >>> >>> The EXPLAINs are: >>> >>> - pgsql 9.5.1: >>> >>>Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) >>> ->

Re: [GENERAL] query reboot pgsql 9.5.1

> >> Now i execute the same in pgsql 9.4.5 and all is fine!!! >> >> The EXPLAINs are: >> >> - pgsql 9.5.1: >> >>Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) >> -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=66) >>-> Bitmap Heap Scan on _gc_cat

Re: [GENERAL] query reboot pgsql 9.5.1

On 03/04/2016 12:09 PM, Felipe de Jesús Molina Bravo wrote: 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 -+-

Re: [GENERAL] Query plan not updated after dropped index

On Thu, Feb 18, 2016 at 11:05 PM, Tom Lane wrote: > Victor Blomqvist writes: > > We just had a major issue on our databases, after a index was replaced a > > user defined function didnt change its query plan to use the new index. > > I'm suspicious that this is some variant of the problem discus

Re: [GENERAL] Query plan not updated after dropped index

Victor Blomqvist writes: > We just had a major issue on our databases, after a index was replaced a > user defined function didnt change its query plan to use the new index. I'm suspicious that this is some variant of the problem discussed a couple days ago: http://www.postgresql.org/message-id/

Re: [GENERAL] Query plan not updated after dropped index

On 2/18/16, Victor Blomqvist wrote: > Hello! > > We just had a major issue on our databases, after a index was replaced a > user defined function didnt change its query plan to use the new index. At > least this is our theory, since the function in question became much slower > and as a result bro

Re: [GENERAL] Query plan not updated after dropped index

The end goal is to get rid of index bloat. If there is a better way to handle this Im all ears! /Victor On Thu, Feb 18, 2016 at 5:21 PM, Oleg Bartunov wrote: > > > On Thu, Feb 18, 2016 at 11:17 AM, Victor Blomqvist wrote: > >> Hello! >> >> We just had a major issue on our databases, after a in

Re: [GENERAL] Query plan not updated after dropped index

On Thu, Feb 18, 2016 at 11:17 AM, Victor Blomqvist wrote: > Hello! > > We just had a major issue on our databases, after a index was replaced a > user defined function didnt change its query plan to use the new index. At > least this is our theory, since the function in question became much slowe

Re: [GENERAL] query from two tables & concat the result

On Wed, Feb 3, 2016 at 8:19 PM, Raymond O'Donnell wrote: > On 03/02/2016 14:05, arnaud gaboury wrote: thetradinghall=> SELECT u.username ||'@'||d.domain as email_address FROM email.mailusers u INNER JOIN email.domainlist d ON (u.domain_id=d.id);

Re: [GENERAL] query from two tables & concat the result

On 03/02/2016 14:05, arnaud gaboury wrote: >>> >>> thetradinghall=> SELECT u.username ||'@'||d.domain as email_address >>> FROM email.mailusers u >>> INNER JOIN >>> email.domainlist d >>> ON >>> (u.domain_id=d.id); >>> >>>email_address >>> --- >>> arnaud

Re: [GENERAL] query from two tables & concat the result

>> >> thetradinghall=> SELECT u.username ||'@'||d.domain as email_address >> FROM email.mailusers u >> INNER JOIN >> email.domainlist d >> ON >> (u.domain_id=d.id); >> >>email_address >> --- >> arnaud.gabo...@thetradinghall.com >> (1 row) >>

Re: [GENERAL] query from two tables & concat the result

On 03/02/2016 13:57, arnaud gaboury wrote: > On Wed, Feb 3, 2016 at 2:19 PM, Raymond O'Donnell wrote: >> On 03/02/2016 13:11, arnaud gaboury wrote: >> >>> Hum hum... >>> -- >>> SELECT u.username ||'@'||d.domain as email_address >>> FROM email.mai

Re: [GENERAL] query from two tables & concat the result

On Wed, Feb 3, 2016 at 1:55 PM, Ricardo Ramírez wrote: > For resetting the id you may want to take a look at the sequence > manipulation functions [1] Problem has been solved by removing the id column. > > Regards, > Ricardo > > [1]http://www.postgresql.org/docs/current/static/functions-sequenc

Re: [GENERAL] query from two tables & concat the result

On Wed, Feb 3, 2016 at 2:19 PM, Raymond O'Donnell wrote: > On 03/02/2016 13:11, arnaud gaboury wrote: > >> Hum hum... >> -- >> SELECT u.username ||'@'||d.domain as email_address >> FROM email.mailusers u >> INNER JOIN >> email.domain d >> ON >> (

Re: [GENERAL] query from two tables & concat the result

On 03/02/2016 13:11, arnaud gaboury wrote: > Hum hum... > -- > SELECT u.username ||'@'||d.domain as email_address > FROM email.mailusers u > INNER JOIN > email.domain d > ON > (u.domain_id=d.domain.id) > WHERE id=1; > > ERROR: missing FROM-clau

  1   2   3   4   5   6   7   8   9   10   >