Re: [PERFORM] Slow query in JDBC

Good catch Jeff. as for which version. We always recommend the latest version. 42.1.4 Dave Cramer da...@postgresintl.com www.postgresintl.com On 29 September 2017 at 06:44, Subramaniam C wrote: > Yes you are right the timestamp which the application was providing was in > seconds whereas the

Re: [PERFORM] Slow query in JDBC

Yes you are right the timestamp which the application was providing was in seconds whereas the query which was using index had a timestamp in milliseconds. So the query was taking time in application. On Fri, Sep 29, 2017 at 12:19 PM, Jeff Janes wrote: > On Thu, Sep 28, 2017 at 2:59 AM, Subraman

Re: [PERFORM] Slow query in JDBC

On Thu, Sep 28, 2017 at 2:59 AM, Subramaniam C wrote: > First output show the output when the query is executed from sql command > line. The second output show when it is executed from the application. AS > per the output it is clear that the when the query is executed through JDBC > its not usin

Re: [PERFORM] Slow query in JDBC

gt;>>> object_table_pkey on object_table (cost=0.42..56727.00 rows=50 >>>> width=64) >>>> >>>> Index Cond: (("timestamp" >= >>>> '0'::bigint) AND ("timestamp" <= '15059

Re: [PERFORM] Slow query in JDBC

(cost=367431.07..373153.32 >>> rows=55526 width=16) >>> >>> -> Unique (cost=367431.07..372459.24 >>> rows=55526 width=24) >>> >>> -> Sort (cost=367431.07..369945.16 >&g

Re: [PERFORM] Slow query in JDBC

s=55526 width=24) >> >> -> Sort (cost=367431.07..369945.16 >> rows=1005634 width=24) >> >> Sort Key: >> health_timeseries_table.mobid DESC, health_timeseries_table."timestamp" &

Re: [PERFORM] Slow query in JDBC

-> Seq Scan on > health_timeseries_table (cost=0.00..267171.00 rows=1005634 width=24) > > > Filter: (("timestamp" >= > '150598950'::bigint) AND ("timestamp" <= '15

Re: [PERFORM] Slow query in JDBC

Filter: (("timestamp" >= '150598950'::bigint) AND ("timestamp" <= '150599040'::bigint)) On Thu, Sep 28, 2017 at 2:56 PM, Pavy Philippe wrote: > https://www.postgresql.org/docs/current/static/auto-explain.html > > > -----Message d&#

Re: [PERFORM] Slow query in JDBC

mance@postgresql.org Objet : Re: [PERFORM] Slow query in JDBC On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C wrote: > I configured cursor_tuple_fraction to 1 but still I am facing the same > issue. Can you show explain (analyze, buffers) of the query when run from psql and run from appli

Re: [PERFORM] Slow query in JDBC

On Thu, Sep 28, 2017 at 10:58 AM, Subramaniam C wrote: > I configured cursor_tuple_fraction to 1 but still I am facing the same > issue. Can you show explain (analyze, buffers) of the query when run from psql and run from application (you can use auto_explain for that if needed, see https://www.p

Re: [PERFORM] Slow query in JDBC

I configured cursor_tuple_fraction to 1 but still I am facing the same issue. Please help. On Thu, Sep 28, 2017 at 2:18 PM, Julien Rouhaud wrote: > On Thu, Sep 28, 2017 at 10:19 AM, Subramaniam C > wrote: > > Hi > > > > When I try to execute the query from sql command line then that query is >

Re: [PERFORM] Slow query in JDBC

On Thu, Sep 28, 2017 at 10:19 AM, Subramaniam C wrote: > Hi > > When I try to execute the query from sql command line then that query is > taking only around 1 sec. But when I execute the query using JDBC(Java) > using preparedStatement then the same query is taking around 10 secs. > > Can you ple

[PERFORM] Slow query in JDBC

Hi When I try to execute the query from sql command line then that query is taking only around 1 sec. But when I execute the query using JDBC(Java) using preparedStatement then the same query is taking around 10 secs. Can you please let us know the reason and how to fix this issue? Thanks and Re

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Tue, Dec 27, 2016 at 3:50 PM, Flávio Henrique wrote: > Hi there, fellow experts! > > I need an advice with query that became slower after 9.3 to 9.6 migration. > > First of all, I'm from the dev team. > > Before migration, we (programmers) made some modifications on query bring > it's average t

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Thu, Jan 5, 2017 at 9:51 AM, Daniel Blanch Bataller wrote: > If just recreating the index now it uses it, it might mean that the index > was bloated, that is, it grew so big that it was cheaper a seq scan. > > I’ve seen another case recently where postgres 9.6 wasn’t using the right > index in

Re: [PERFORM] Slow query with 3 table joins

erent values in > FIELD.FIELD_NAME > > Gerardo > > - Mensaje original - > > De: "Alessandro Ferrucci" > > Para: pgsql-performance@postgresql.org > > Enviados: Miércoles, 26 de Abril 2017 0:19:37 > > Asunto: Re: [PERFORM] Slow query with 3 ta

Re: [PERFORM] Slow query with 3 table joins

Dave - I had re-ran ANALYZE and VACUUM on all the tables and that did not seem to have fixed the issue (the query still took a long time, however I did not let it finish to produce a full EXPLAIN plan. However - after creating an index on FIELD(FIELD_NAME,UNIT_ID) and now the query runs very fast

Re: [PERFORM] Slow query with 3 table joins

" > Para: pgsql-performance@postgresql.org > Enviados: Miércoles, 26 de Abril 2017 0:19:37 > Asunto: Re: [PERFORM] Slow query with 3 table joins > > > > After about 40 inutes the slow query finally finished and the result > of the EXPLAIN plan can be found here: > > &

Re: [PERFORM] Slow query with 3 table joins

- Mensaje original - > De: "Alessandro Ferrucci" > Para: pgsql-performance@postgresql.org > Enviados: Miércoles, 26 de Abril 2017 0:19:37 > Asunto: Re: [PERFORM] Slow query with 3 table joins > > > > After about 40 inutes the slow query finall

Re: [PERFORM] Slow query with 3 table joins

> Hi Eskil - > > > The I believe the id-field you're referring to is the UNIT.UNIT_ID, I > could change this to a varchar, however that column is not used in the > query in question, so that wouldn't have any effect on the query's > performance. Sorry, I did not notice that the column "unit_id

Re: [PERFORM] Slow query with 3 table joins

Hi Dave - thank you very much for all this advice! I will try each of these and post back results (some of this stuff, like creating the index, which is happening now, takes a very long time). Thanks again for all these pointers. Cheers, Alessandro On Wed, Apr 26, 2017 at 12:12 AM, David Rowle

Re: [PERFORM] Slow query with 3 table joins

Hi Eskil - The I believe the id-field you're referring to is the UNIT.UNIT_ID, I could change this to a varchar, however that column is not used in the query in question, so that wouldn't have any effect on the query's performance. Just for curiosity - I have changed the ANSWER.ANS datatype to a

Re: [PERFORM] Slow query with 3 table joins

tis 2017-04-25 klockan 23:19 -0400 skrev Alessandro Ferrucci: > After about 40 inutes the slow query finally finished and the result > of the EXPLAIN plan can be found here: > > > https://explain.depesz.com/s/BX22 > > > Thanks, > Alessandro Ferrucci I'm not so familiar with the index implement

Re: [PERFORM] Slow query with 3 table joins

On 26 April 2017 at 15:19, Alessandro Ferrucci wrote: > After about 40 inutes the slow query finally finished and the result of the > EXPLAIN plan can be found here: > > https://explain.depesz.com/s/BX22 > Index Scan using field_unit_id_idx on field (cost=0.00..8746678.52 > rows=850149 width=8)

Re: [PERFORM] Slow query with 3 table joins

After about 40 inutes the slow query finally finished and the result of the EXPLAIN plan can be found here: https://explain.depesz.com/s/BX22 Thanks, Alessandro Ferrucci On Tue, Apr 25, 2017 at 11:10 PM, Alessandro Ferrucci < alessandroferru...@gmail.com> wrote: > Hello - I am migrating a curre

[PERFORM] Slow query with 3 table joins

Hello - I am migrating a current system to PostgreSQL and I am having an issue with a relatively straightforward query being extremely slow. The following are the definitions of the tables: CREATE TABLE popt_2017.unit ( id serial NOT NULL, unit_id text, batch_id text, create_date timestam

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

Thank you for the reply. I had been trying to find that option for awhile now. On Fri, Jan 6, 2017 at 12:51 PM, Michael Paquier wrote: > On Fri, Jan 6, 2017 at 6:14 AM, Filipe Oliveira > wrote: > > Can you remove me from your mailing list? > > There is an unsubscribe action here: > https://www.

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Fri, Jan 6, 2017 at 6:14 AM, Filipe Oliveira wrote: > Can you remove me from your mailing list? There is an unsubscribe action here: https://www.postgresql.org/community/lists/subscribe/ -- Michael -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make change

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

Can you remove me from your mailing list? Thanks.

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique wrote: > @Merlin Moncure >> >> Big gains (if any) are likely due to indexing strategy. >> I do see some suspicious casting, for example: >> Join Filter: ((four_charlie.delta_tango)::integer = >> (six_quebec.golf_bravo)::integer) >> Are you casting i

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

Hi, If just recreating the index now it uses it, it might mean that the index was bloated, that is, it grew so big that it was cheaper a seq scan. I’ve seen another case recently where postgres 9.6 wasn’t using the right index in a query, I was able to reproduce the issue crafting index bigger,

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Thu, Jan 5, 2017 at 10:51 AM, Flávio Henrique wrote: > Replying your comment, I think they tunned the server: > effective_cache_size = 196GB > shared_buffers = 24GB (this shouldn't be higher?) Probably not, although it may be a good idea to try settings either side of that (say, 16GB and 32GB

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

Hi all! Sorry the delay (holidays). Well, the most expensive sequencial scan was solved. I asked the db team to drop the index and recreate it and guess what: now postgresql is using it and the time dropped. (thank you, @Gerardo Herzig!) I think there's still room for improvement, but the problem

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique wrote: > Hi there, fellow experts! > > I need an advice with query that became slower after 9.3 to 9.6 migration. > > First of all, I'm from the dev team. > > Before migration, we (programmers) made some modifications on query bring > it's average t

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

On Tue, Dec 27, 2016 at 5:50 PM, Flávio Henrique wrote: > I can see some buffers written that tells me > that something is wrong. Try running VACUUM FREEZE ANALYZE on all tables involved in the query (or just run it as a superuser on the whole database). Do *not* use the FULL option. Among oth

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

The biggest impact on performance you can achieve is by using a materialized view. if it’s so heavily used as you said, even 2-3 seconds in a multiuser OLTP environment still unacceptable under my point of view. I don’t know if this is the case but if you have 1000 users connecting at 8 am all a

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

> > Hi there, fellow experts! > > > I need an advice with query that became slower after 9.3 to 9.6 > migration. > > > First of all, I'm from the dev team. > > > Before migration, we (programmers) made some modifications on query > bring it's average time from 8s to 2-3s. > > > As this que

[PERFORM] Slow query after 9.3 to 9.6 migration

Hi there, fellow experts! I need an advice with query that became slower after 9.3 to 9.6 migration. First of all, I'm from the dev team. Before migration, we (programmers) made some modifications on query bring it's average time from 8s to 2-3s. As this query is the most executed on our system

Re: [PERFORM] Slow query question

Tom, Thank you for a thorough answer. We’ll try the 2-column index. Regards, Andrey Povazhnyi > On Dec 6, 2016, at 6:33 PM, Tom Lane wrote: > > Andrey Povazhnyi writes: >> We’ve got a strange planner behavior on a query to one of our bigger tables >> after we upgraded to postgres 9.6.1 recen

Re: [PERFORM] Slow query question

Andrey Povazhnyi writes: > We’ve got a strange planner behavior on a query to one of our bigger tables > after we upgraded to postgres 9.6.1 recently. The basic problem with this query is that there are no good alternatives. The planner believes there are about 53K rows matching the WHERE condit

[PERFORM] Slow query question

Hello, List. We’ve got a strange planner behavior on a query to one of our bigger tables after we upgraded to postgres 9.6.1 recently. The table (schema here http://pastebin.com/nRAny4bw ) has 28m+ rows and is used to store chat messages for different chat rooms (s

Re: [PERFORM] Slow query with big tables

On Sat, Aug 27, 2016 at 18:33 GMT+03:00, Jeff Janes wrote: > Partitioning the Feature and Point tables on measurement_time (or > measurement_start_time, > you are not consistent on what it is called) might be helpful. However, > measurement_time does not exist in those tables, so you would first

Re: [PERFORM] Slow query with big tables

On Fri, Aug 26, 2016 at 6:17 AM, Tommi K wrote: > Hello, > thanks for the response. I did not get the response to my email even > though I am subscribed to the pgsql-performance mail list. Let's hope that > I get the next one :) > > Increasing work_mem did not have great impact on the performance

Re: [PERFORM] Slow query with big tables

On Sat, Aug 27, 2016 at 7:13 AM, Craig James wrote: > On Fri, Aug 26, 2016 at 9:11 PM, Jim Nasby > wrote: > >> On 8/26/16 3:26 PM, Mike Sofen wrote: >> >>> Is there way to keep query time constant as the database size grows. >>> >> >> No. More data == more time. Unless you find a way to break th

Re: [PERFORM] Slow query with big tables

Craig James writes: > Straight hash-table indexes (which Postgres doesn't use) have O(1) access > time. The amount of data has no effect on the access time. This is wishful thinking --- once you have enough data, O(1) goes out the window. For example, a hash index is certainly not going to conti

Re: [PERFORM] Slow query with big tables

On Fri, Aug 26, 2016 at 9:11 PM, Jim Nasby wrote: > On 8/26/16 3:26 PM, Mike Sofen wrote: > >> Is there way to keep query time constant as the database size grows. >> > > No. More data == more time. Unless you find a way to break the laws of > physics. > Straight hash-table indexes (which Postgr

Re: [PERFORM] Slow query with big tables

rformance@postgresql.org > *Subject:* Re: [PERFORM] Slow query with big tables > > > > Ok, sorry that I did not add the original message. I thought that it would > be automatically added to the message thread. > > > > Here is the question again: > > > > I

Re: [PERFORM] Slow query with big tables

On 8/26/16 3:26 PM, Mike Sofen wrote: Is there way to keep query time constant as the database size grows. No. More data == more time. Unless you find a way to break the laws of physics. Should I use partitioning or partial indexes? Neither technique is a magic bullet. I doubt either woul

Re: [PERFORM] Slow query with big tables

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Tommi K Sent: Friday, August 26, 2016 7:25 AM To: Craig James Cc: andreas kretschmer ; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query with big tables Ok, sorry

Re: [PERFORM] Slow query with big tables

Ok, sorry that I did not add the original message. I thought that it would be automatically added to the message thread. Here is the question again: Is there way to keep query time constant as the database size grows. Should I use partitioning or partial indexes? Thanks, Tommi Kaksonen > Hell

Re: [PERFORM] Slow query with big tables

On Fri, Aug 26, 2016 at 6:17 AM, Tommi K wrote: > Hello, > thanks for the response. I did not get the response to my email even > though I am subscribed to the pgsql-performance mail list. Let's hope that > I get the next one :) > Please include the email you are replying to when you respond. It

Re: [PERFORM] Slow query with big tables

Hello, thanks for the response. I did not get the response to my email even though I am subscribed to the pgsql-performance mail list. Let's hope that I get the next one :) Increasing work_mem did not have great impact on the performance. But I will try to update the PostgreSQL version to see if i

Re: [PERFORM] Slow query with big tables

Tommi Kaksonen wrote: > ---Version--- > PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit current point release for 9.2 is 9.2.18, you are some years behind. The plan seems okay for me, apart from the on-disk sort: increase work_mem to avoid that. If i where you i would switch to PG

[PERFORM] Slow query with big tables

Hello, I have the following tables and query. I would like to get some help to find out why it is slow and how its performance could be improved. Thanks, Tommi K. *--Table definitions---* CREATE TABLE "Measurement" ( id bigserial NOT NULL, product_id bigserial NOT NULL, nominal_data_id bi

Re: [PERFORM] Slow query help

>I ask your help to solve a slow query which is taking more than 14 seconds to >be executed. >Maybe I am asking too much both from you and specially from postgresql, as it >is really huge, envolving 16 tables. > >Explain: >http://explain.depesz.com/s/XII9 > >Schema: >http://adj.com.br/erp/data_

[PERFORM] Slow query help

Hi, I ask your help to solve a slow query which is taking more than 14 seconds to be executed. Maybe I am asking too much both from you and specially from postgresql, as it is really huge, envolving 16 tables. Explain: http://explain.depesz.com/s/XII9 Schema: http://adj.com.br/erp/data_schem

Re: [PERFORM] Slow query in trigger function

These are the queries I used to get the execution planer use the index scan instead of the sequential scan: IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = old.key) => sequential scan IF NOT EXISTS (SELECT 1 FROM custom_data WHERE key = old.key LIMIT 1) => sequential scan IF NOT EXISTS (SEL

Re: [PERFORM] Slow query in trigger function

Guido Niewerth writes: > And this is the execution plan. It looks like it does a slow sequential scan > where it´s able to do an index scan: > 2015-11-02 17:42:10 CET LOG: duration: 5195.673 ms plan: > Query Text: SELECT NOT EXISTS( SELECT 1 FROM custom_data > WHERE key = old.

Re: [PERFORM] Slow query in trigger function

I needed to set up the trigger function again, so here it is: CREATE OR REPLACE FUNCTION public.fn_trigger_test () RETURNS trigger AS $body$ DECLARE start TIMESTAMP; BEGIN start := timeofday(); IF TG_OP = 'UPDATE' THEN IF NOT EXISTS( SELECT key FROM custom_data WHERE ke

Re: [PERFORM] Slow query in trigger function

Guido Niewerth writes: > As you can see there´s a huge runtime difference between the select query > used in the trigger function and the one run from the SQL editor. contrib/auto_explain might be useful in seeing what's going on, in particular it would tell us whether or not a different plan is

[PERFORM] Slow query in trigger function

Hello, I´ve got a table custom_data which essentially contains a number of key/value pairs. This table holds a large number (about 40M) of records and I need the distinct keys and values for some reasons. Selecting those distinct data takes a couple of seconds, so I decided to maintain a separa

Re: [PERFORM] Slow Query

OK. If you benchmark that correctly, then it seems that adding some redundant search can get the query faster in some special cases. And without further info, I can not see any reason. 2015-08-14 14:35 GMT+09:00 Robert Campbell : > Hi, > > My mistake, didnt apply the sub query properly the firs

Re: [PERFORM] Slow Query

Is the "Vacancy"."ID" a primary key? Or is unique in Vacancy table?

Re: [PERFORM] Slow Query

Hi, Doing this returns 0 records On Thu, Aug 13, 2015 at 7:22 PM, 林士博 [via PostgreSQL] < ml-node+s1045698n5862008...@n5.nabble.com> wrote: > In the 'not exists' cluster, you do not have to search table "Vacancy as > v" again. > I think it would be faster to use the outer Vacancy table as below.

Re: [PERFORM] Slow Query

In the 'not exists' cluster, you do not have to search table "Vacancy as v" again. I think it would be faster to use the outer Vacancy table as below. In your case, that do the same work. NOT EXISTS ( SELECT 1 FROM "CategoryOption_TableRow" "ct126" WHERE "Vacancy"."Template

Re: [PERFORM] Slow Query

Hi Vik, Thanks for your feedback, very helpful. I modified your query slightly, this will return all vacancy templates and all level 1 vacancies which arent templates, and does so in about ~800-900ms less, an great improvement on the original query. SELECT "Vacancy"."ID", "Vacancy"."JobTi

Re: [PERFORM] Slow Query

On 08/12/2015 04:34 AM, robbyc wrote: > Hi, > > I am new to optimizing queries and i'm getting a slow running time > (~1.5secs) with the following SQL: Before mucking about with work_mem and indexes, the first thing to do is rewrite this query correctly. Here are just some of the things wrong wi

Re: [PERFORM] Slow Query

On Wed, Aug 12, 2015 at 3:29 PM, robbyc wrote: > Hi Venkata, > > work_mem was set to 72MB, increased to 144MB, no change. > Increasing work_mem depends on various other factors like Table size (amount of data being sorted), available memory etc. > Added an index of type varchar_pattern_ops to

Re: [PERFORM] Slow Query

Hi Venkata, work_mem was set to 72MB, increased to 144MB, no change. Added an index of type varchar_pattern_ops to Vacancy.JobTitle, this did not help either. On Wed, Aug 12, 2015 at 2:09 PM, Venkata Balaji N [via PostgreSQL] < ml-node+s1045698n5861839...@n5.nabble.com> wrote: > On Wed, Aug 12,

Re: [PERFORM] Slow Query

On Wed, Aug 12, 2015 at 12:34 PM, robbyc wrote: > Hi, > > I am new to optimizing queries and i'm getting a slow running time > (~1.5secs) with the following SQL: > > SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle", > "Vacancy"."DateCreated", "Vacancy"."CustomAccess" > , "Department"."Name"

[PERFORM] Slow Query

Hi, I am new to optimizing queries and i'm getting a slow running time (~1.5secs) with the following SQL: SELECT distinct("Vacancy"."ID"), "Vacancy"."JobTitle", "Vacancy"."DateCreated", "Vacancy"."CustomAccess" , "Department"."Name" as "Department", list("Occupation"."Name") as "Occupation", "Vac

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

On 24/06/15 09:05, Jim Nasby wrote: > On 6/19/15 9:57 AM, Ian Pushee wrote: >> >> >> On 6/19/2015 10:47 AM, Andreas Kretschmer wrote: Explain Analyze outputs (links as requested): Default plan: http://explain.depesz.com/s/ib3k Forced index (random_page_cost=1.0): http://explain.depes

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

On 6/19/15 9:57 AM, Ian Pushee wrote: On 6/19/2015 10:47 AM, Andreas Kretschmer wrote: Explain Analyze outputs (links as requested): Default plan: http://explain.depesz.com/s/ib3k Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP Software/Hardware: PGSql 9.2.1, Windows 8.1

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Friday, June 19, 2015 11:07 AM To: Ian Pushee; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query (planner insisting on using

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Ian Pushee Sent: Friday, June 19, 2015 10:54 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query (planner insisting on using 'external

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

On 6/19/2015 10:47 AM, Andreas Kretschmer wrote: Explain Analyze outputs (links as requested): Default plan: http://explain.depesz.com/s/ib3k Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM All pgsql settings are at the

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

On 6/19/2015 10:46 AM, Igor Neyman wrote: Probably events_confidnce index is not very selective, that's why optimizer prefers seq scan. I'd try to create an index on (name, eventspy_id, camera_id, type, status). Also, the recent 9.2 is 9.2.13, you should upgrade. Regards, Igor Neyman Hi I

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Ian Pushee Sent: Friday, June 19, 2015 10:34 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Slow query (planner insisting on using 'external

Re: [PERFORM] Slow query (planner insisting on using 'external merge' sort type)

> Explain Analyze outputs (links as requested): > Default plan: http://explain.depesz.com/s/ib3k > Forced index (random_page_cost=1.0): http://explain.depesz.com/s/lYaP > > Software/Hardware: PGSql 9.2.1, Windows 8.1, 8GB RAM > All pgsql settings are at their defaults. increase work_mem. per ses

[PERFORM] Slow query (planner insisting on using 'external merge' sort type)

Hi Folks, This is my first time posting here, so hopefully I manage to convey all the information needed. We have a simple query that just started giving us problems in production when the number of rows gets too large (>100k). The issue seems to be that the planner wants to sort the rows using

Re: [PERFORM] Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated

On Thu, Jun 11, 2015 at 7:18 PM, Sasa Vilic wrote: > Hi, > > I have a query that takes ridiculously long to complete (over 500ms) but if > I disable nested loop it does it really fast (24.5ms) > > Here are links for > * first request (everything enabled): http://explain.depesz.com/s/Q1M > * second

Re: [PERFORM] Slow query - lots of temporary files.

On 10 June 2015 at 16:50, Tomas Vondra wrote: > > > The problematic piece of the explain plan is this: > > -> Merge Join (cost=4384310.92..21202716.78 rows=6664163593 > width=390)" >Output: a.ut, c.gt, b.go, b.gn, d.au" >Merge Cond: ((c.ut)::text = (d.rart_id)

[PERFORM] Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated

Hi, I have a query that takes ridiculously long to complete (over 500ms) but if I disable nested loop it does it really fast (24.5ms) Here are links for * first request (everything enabled): http://explain.depesz.com/s/Q1M * second request (nested loop disabled): http://explain.depesz.com/s/9Z

Re: [PERFORM] Slow query - lots of temporary files.

On 06/10/15 15:42, Johann Spies wrote: On 10 June 2015 at 15:02, Claudio Freire mailto:klaussfre...@gmail.com>> wrote: The joins are different on both versions, and the most likely culprit is the join against D. It's probably wrong, and the first query is building a cartesian prod

Re: [PERFORM] Slow query - lots of temporary files.

On 10 June 2015 at 15:02, Claudio Freire wrote: > > The joins are different on both versions, and the most likely culprit > is the join against D. It's probably wrong, and the first query is > building a cartesian product. > > Without more information about the schema it's difficult to be sure th

Re: [PERFORM] Slow query - lots of temporary files.

On Wed, Jun 10, 2015 at 9:39 AM, Johann Spies wrote: > COPY > (SELECT A.ut, > B.go AS funding_org, > B.gn AS grant_no, > C.gt AS thanks, > D.au >FROM isi.funding_text C, > isi.rauthor D, > isi.africa_uts A >LEFT JOIN isi.funding_org

[PERFORM] Slow query - lots of temporary files.

I have stopped this query after about 16 hours. At the same time I ran a 'explain analyze' on the same query to find out why it took so long. These two processes generated temporary files of 173GB in /var/lib/postgresql/9.4/main/base/pgsql_tmp. COPY (SELECT A.ut, B.go AS funding_org,

Re: [PERFORM] slow query

Sathish Nelson writes: > am connecting three tables in query. one table have 73000 records > another two tables have 138000 records. > but its take 12 sec for show 12402 rows in tables Increasing work_mem would make those sort steps faster ... regards, tom lane -- Sent

Re: [PERFORM] slow query

On 11/02/15 07:41, Sathish Nelson wrote: > am connecting three tables in query. one table have 73000 records > > another two tables have 138000 records. > > but its take 12 sec for show 12402 rows in tables you need more work_mem. The query plan shows multiple external sort nodes. Otherwise, th

[PERFORM] slow query

am connecting three tables in query. one table have 73000 records another two tables have 138000 records. but its take 12 sec for show 12402 rows in tables Tables Structure: Items Table CREATE TABLE "C_SAM_Master".items ( itemno integer NOT NULL, itemname character varying(250) NOT NULL,

Re: [PERFORM] Slow query

Ross Elliott-2 wrote > Maybe someone can explain this. The following SQL will reproduce our > issue: > DROP TABLE IF EXISTS t1 CASCADE; > CREATE TABLE t1 (name text, > state text); > CREATE INDEX t1_name ON t1(name); > CREATE INDEX t1_state ON t1(state); > CREATE INDEX t1_name_stat

[PERFORM] Slow query

Maybe someone can explain this. The following SQL will reproduce our issue: DROP TABLE IF EXISTS t1 CASCADE; CREATE TABLE t1 (name text, state text); CREATE INDEX t1_name ON t1(name); CREATE INDEX t1_state ON t1(state); CREATE INDEX t1_name_state ON t1(name,state); -- Create some

Re: [PERFORM] Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

> > No, it doesn't. Read it again ... or read up on row comparisons, > if you're unfamiliar with that notation. The above queries are > exactly equivalent per spec. > Wow, this is great. Thanks.

Re: [PERFORM] Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

johno writes: > On Tue, Jul 22, 2014 at 4:53 AM, Tom Lane wrote: >> johno writes: >>> The obvious query is >>> SELECT * FROM register_uz_accounting_entities >>> WHERE effective_on > '2014-07-11' OR (effective_on = '2014-07-11' AND >>> id > 1459) >>> ORDER BY effective_on, id >>> LIMIT 100 >> A

Re: [PERFORM] Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

On Tue, Jul 22, 2014 at 4:53 AM, Tom Lane wrote: > johno writes: > > I am trying to optimize a simple query that returns first 100 rows that > > have been updated since a given timestamp (ordered by timestamp and id > > desc). If there are several rows with the same timestamp I need to a > > se

Re: [PERFORM] Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

johno writes: > I am trying to optimize a simple query that returns first 100 rows that > have been updated since a given timestamp (ordered by timestamp and id > desc). If there are several rows with the same timestamp I need to a > second condition, that states that I want to return rows having

[PERFORM] Slow query with indexed ORDER BY and LIMIT when using OR'd conditions

Hi there, I am trying to optimize a simple query that returns first 100 rows that have been updated since a given timestamp (ordered by timestamp and id desc). If there are several rows with the same timestamp I need to a second condition, that states that I want to return rows having the given t

Re: [PERFORM] Slow query

r@ > [mailto: > pgsql-performance-owner@ > ] On Behalf Of David Johnston > Sent: Friday, March 07, 2014 11:53 AM > To: > pgsql-performance@ > Subject: Re: [PERFORM] Slow query > > Bikram Kesari Naik wrote >> Hi, >> >> I have a view which joins mu

Re: [PERFORM] Slow query

Johnston Sent: Friday, March 07, 2014 11:53 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow query Bikram Kesari Naik wrote > Hi, > > I have a view which joins multiple tables to give me a result. It > takes more than a minute to give me the result on psql prompt whe

Re: [PERFORM] Slow query

Bikram Kesari Naik wrote > Hi, > > I have a view which joins multiple tables to give me a result. It takes > more than a minute to give me the result on psql prompt when I select all > the data from that view. > The single CPU which is used to run this query is utilized 100%.Even if I > fire a c

  1   2   3   4   5   6   >