pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with query
30+ messages / 14 participants
[nested] [flat]
* problem with query
@ 2013-09-12 20:34 Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-13 14:49 ` Re: problem with query Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
0 siblings, 2 replies; 30+ messages in thread
From: Roberto Scattini @ 2013-09-12 20:34 UTC (permalink / raw)
To: pgsql-general
hi, today we discovered that this query doesn't return the expected values:
SELECT DISTINCT
p.id, p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS
"Funcion", to_char(da.f_ingreso_pg, 'dd/mm/yyyy') AS "Fecha Ingreso PG",
e.estado AS "Estado", to_char(pe.fecha, 'dd/mm/yyyy') AS "Fecha Estado"
FROM personal.personas AS p
LEFT JOIN personal.personas_da_pg AS da ON p.id=da.id_persona
LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id
LEFT JOIN personal.estados AS e ON pe.id_estado=e.id
LEFT JOIN procu_departamento AS d ON d.id=da.id_departamento
LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama,
4)||'000'=dto.c_organigrama
LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama,
3)||'0000'=dir.c_organigrama
LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama,
2)||'00000'=dg.c_organigrama
LEFT JOIN personal.funciones AS pf ON pf.id=da.funcion_id
LEFT JOIN personal.profesiones AS pp ON pp.id=p.id_profesion
WHERE p.apellido ilike '%nuñez%'
ORDER BY "Apellido"
the exact same query with ilike '%NUÑEZ%' works OK...
we are using postgresql 9.1 from ubuntu packages and the database encoding
is (sadly) SQL_ASCII
can anybody point me in the right direction to solve this issue?
--
Roberto Scattini
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
@ 2013-09-12 21:22 ` Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Chris Curvey @ 2013-09-12 21:22 UTC (permalink / raw)
To: pgsql-general
On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini <
roberto.scattini@gmail.com> wrote:
> hi, today we discovered that this query doesn't return the expected values:
>
> SELECT DISTINCT
> p.id, p.apellido AS "Apellido", p.nombre AS "Nombre", pf.nombre AS
> "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/yyyy') AS "Fecha Ingreso PG",
> e.estado AS "Estado", to_char(pe.fecha, 'dd/mm/yyyy') AS "Fecha Estado"
> FROM personal.personas AS p
> LEFT JOIN personal.personas_da_pg AS da ON p.id=da.id_persona
> LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id
> LEFT JOIN personal.estados AS e ON pe.id_estado=e.id
> LEFT JOIN procu_departamento AS d ON d.id=da.id_departamento
> LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama,
> 4)||'000'=dto.c_organigrama
> LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama,
> 3)||'0000'=dir.c_organigrama
> LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama,
> 2)||'00000'=dg.c_organigrama
> LEFT JOIN personal.funciones AS pf ON pf.id=da.funcion_id
> LEFT JOIN personal.profesiones AS pp ON pp.id=p.id_profesion
> WHERE p.apellido ilike '%nuñez%'
> ORDER BY "Apellido"
>
> the exact same query with ilike '%NUÑEZ%' works OK...
>
> we are using postgresql 9.1 from ubuntu packages and the database encoding
> is (sadly) SQL_ASCII
>
> can anybody point me in the right direction to solve this issue?
>
> --
> Roberto Scattini
>
If you could tells what you are expecting, and what you are actually
getting, that would be helpful.
--
The person who says it cannot be done should not interrupt the person who
is doing it. -- Chinese Proverb
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
@ 2013-09-12 21:33 ` Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
0 siblings, 1 reply; 30+ messages in thread
From: Roberto Scattini @ 2013-09-12 21:33 UTC (permalink / raw)
To: pgsql-general
On Thu, Sep 12, 2013 at 6:22 PM, Chris Curvey <chris@chriscurvey.com> wrote:
>
>
> On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini <
> roberto.scattini@gmail.com> wrote:
>>
>>
>> we are using postgresql 9.1 from ubuntu packages and the database
>> encoding is (sadly) SQL_ASCII
>>
>> can anybody point me in the right direction to solve this issue?
>>
>> --
>> Roberto Scattini
>>
>
> If you could tells what you are expecting, and what you are actually
> getting, that would be helpful.
>
>
hi chris, sorry, the query with ilike '%NUÑEZ%' is returning 4 rows and
the query with ilike '%nuñez%' is returning 0 rows.
a simplified version of the query with his results:
pgn=# set client_encoding = SQL_ASCII;
SET
pgon=# SELECT DISTINCT
p.id, p.apellido, p.nombre
FROM personal.personas AS p
WHERE p.apellido ilike '%nuñez%';
id | apellido | nombre
----+----------+--------
(0 rows)
pgon=# SELECT DISTINCT
p.id, p.apellido, p.nombre
FROM personal.personas AS p
WHERE p.apellido ilike '%NUÑEZ%';
id | apellido | nombre
-------+----------------+-----------------
39489 | NUÑEZ | JUAN
39937 | PEREZ NUÑEZ | FRANCISCO
39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO
40220 | NUÑEZ VERA | MANUEL SANTIAGO
(4 rows)
thanks!
--
Roberto Scattini
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
@ 2013-09-12 21:49 ` Chris Curvey <chris@chriscurvey.com>
2013-09-12 22:02 ` Re: problem with query Bosco Rama <postgres@boscorama.com>
2013-09-12 22:03 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: Chris Curvey @ 2013-09-12 21:49 UTC (permalink / raw)
To: Roberto Scattini <roberto.scattini@gmail.com>; +Cc: pgsql-general
On Thu, Sep 12, 2013 at 5:33 PM, Roberto Scattini <
roberto.scattini@gmail.com> wrote:
>
>
> On Thu, Sep 12, 2013 at 6:22 PM, Chris Curvey <chris@chriscurvey.com>wrote:
>
>>
>>
>> On Thu, Sep 12, 2013 at 4:34 PM, Roberto Scattini <
>> roberto.scattini@gmail.com> wrote:
>>
>>>
>>> we are using postgresql 9.1 from ubuntu packages and the database
>>> encoding is (sadly) SQL_ASCII
>>>
>>> can anybody point me in the right direction to solve this issue?
>>>
>>> --
>>> Roberto Scattini
>>>
>>
>> If you could tells what you are expecting, and what you are actually
>> getting, that would be helpful.
>>
>>
> hi chris, sorry, the query with ilike '%NUÑEZ%' is returning 4 rows and
> the query with ilike '%nuñez%' is returning 0 rows.
>
> a simplified version of the query with his results:
>
>
> pgn=# set client_encoding = SQL_ASCII;
> SET
> pgon=# SELECT DISTINCT
> p.id, p.apellido, p.nombre
> FROM personal.personas AS p
> WHERE p.apellido ilike '%nuñez%';
> id | apellido | nombre
> ----+----------+--------
> (0 rows)
>
> pgon=# SELECT DISTINCT
> p.id, p.apellido, p.nombre
> FROM personal.personas AS p
> WHERE p.apellido ilike '%NUÑEZ%';
> id | apellido | nombre
> -------+----------------+-----------------
> 39489 | NUÑEZ | JUAN
> 39937 | PEREZ NUÑEZ | FRANCISCO
> 39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO
> 40220 | NUÑEZ VERA | MANUEL SANTIAGO
> (4 rows)
>
>
>
Is this just a case-sentitvity issue? if personas.apellido is a varchar
field, then I think that's your trouble. (it would have to be citext in
order for "nunez" = "NUNEZ".)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
@ 2013-09-12 22:02 ` Bosco Rama <postgres@boscorama.com>
2013-09-12 22:07 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Bosco Rama @ 2013-09-12 22:02 UTC (permalink / raw)
To: pgsql-general
On 09/12/13 14:49, Chris Curvey wrote:
> Is this just a case-sentitvity issue? if personas.apellido is a varchar
> field, then I think that's your trouble. (it would have to be citext in
> order for "nunez" = "NUNEZ".)
He's using 'ilike' in his query, so this is more likely to be a
locale/charset/collation/encoding issue.
Roberto, what encodings are in use by the client and the server/DB?
HTH,
Bosco.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 22:02 ` Re: problem with query Bosco Rama <postgres@boscorama.com>
@ 2013-09-12 22:07 ` Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 22:19 ` Re: problem with query Rodrigo Gonzalez <rjgonzale.lists@gmail.com>
2013-09-12 22:22 ` Re: problem with query Bosco Rama <postgres@boscorama.com>
0 siblings, 2 replies; 30+ messages in thread
From: Roberto Scattini @ 2013-09-12 22:07 UTC (permalink / raw)
To: Bosco Rama <postgres@boscorama.com>; +Cc: pgsql-general
On Thu, Sep 12, 2013 at 7:02 PM, Bosco Rama <postgres@boscorama.com> wrote:
> On 09/12/13 14:49, Chris Curvey wrote:
> > Is this just a case-sentitvity issue? if personas.apellido is a varchar
> > field, then I think that's your trouble. (it would have to be citext in
> > order for "nunez" = "NUNEZ".)
>
> He's using 'ilike' in his query, so this is more likely to be a
> locale/charset/collation/encoding issue.
>
> Roberto, what encodings are in use by the client and the server/DB?
>
>
hi bosco, in fact i believe that i have that problem... but i cant
undestand why and how to fix it.
the database has SQL_ASCII encoding, and my client... i am not sure, when i
connect directly with psql from localhost i must set client encoding to
SQL_ASCII or i receive an "invalid utf8 sequence" error...
but the problem is there, from psql, from pgadmin and from php/apache (my
webservers)
thanks
--
Roberto Scattini
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 22:02 ` Re: problem with query Bosco Rama <postgres@boscorama.com>
2013-09-12 22:07 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
@ 2013-09-12 22:19 ` Rodrigo Gonzalez <rjgonzale.lists@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: Rodrigo Gonzalez @ 2013-09-12 22:19 UTC (permalink / raw)
To: Roberto Scattini <roberto.scattini@gmail.com>; +Cc: Bosco Rama <postgres@boscorama.com>; pgsql-general
On Thu, 12 Sep 2013 19:07:04 -0300
Roberto Scattini <roberto.scattini@gmail.com> wrote:
> On Thu, Sep 12, 2013 at 7:02 PM, Bosco Rama <postgres@boscorama.com>
> wrote:
>
> > On 09/12/13 14:49, Chris Curvey wrote:
> > > Is this just a case-sentitvity issue? if personas.apellido is a
> > > varchar field, then I think that's your trouble. (it would have
> > > to be citext in order for "nunez" = "NUNEZ".)
> >
> > He's using 'ilike' in his query, so this is more likely to be a
> > locale/charset/collation/encoding issue.
> >
> > Roberto, what encodings are in use by the client and the server/DB?
> >
> >
> hi bosco, in fact i believe that i have that problem... but i cant
> undestand why and how to fix it.
>
> the database has SQL_ASCII encoding, and my client... i am not sure,
> when i connect directly with psql from localhost i must set client
> encoding to SQL_ASCII or i receive an "invalid utf8 sequence" error...
>
> but the problem is there, from psql, from pgadmin and from php/apache
> (my webservers)
Without being an expert....dont use sql_ascii, dump, recreate the DB as
utf8 and you will get what you want.
>
>
> thanks
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 22:02 ` Re: problem with query Bosco Rama <postgres@boscorama.com>
2013-09-12 22:07 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
@ 2013-09-12 22:22 ` Bosco Rama <postgres@boscorama.com>
1 sibling, 0 replies; 30+ messages in thread
From: Bosco Rama @ 2013-09-12 22:22 UTC (permalink / raw)
To: Roberto Scattini <roberto.scattini@gmail.com>; +Cc: pgsql-general
On 09/12/13 15:07, Roberto Scattini wrote:
> hi bosco, in fact i believe that i have that problem... but i cant
> undestand why and how to fix it.
>
> the database has SQL_ASCII encoding, and my client... i am not sure, when i
> connect directly with psql from localhost i must set client encoding to
> SQL_ASCII or i receive an "invalid utf8 sequence" error...
>
> but the problem is there, from psql, from pgadmin and from php/apache (my
> webservers)
Well, the bytes that get encoded (in UTF8) for lowercase 'enye' is
x'c3b1' and for the uppercase version is x'c391' thus when they are
compared as straight bytes they will never be considered equal wrt
case-insensitivity.
The "invalid utf8 sequence" error is due to, as it says, an invalid
utf8 sequence in your text strings. This is due to the lack of any
enforcement provided by the SQL_ASCII encoding that was probably in
effect when the data was initially inserted.
To get to a point where you can use your data in its proper encoding
you will need to identify which strings are invalid and then dump
the DB and restore it into an appropriately initialized DB. You could
use the restore process to help you find the bad strings but that is
tedious as all hell. Better to scan you strings somehow and locate the
ones that are invalid and fix them prior to the dump.
HTH,
Bosco.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
@ 2013-09-12 22:03 ` Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 22:24 ` Re: problem with query John R Pierce <pierce@hogranch.com>
2013-09-12 22:57 ` Re: problem with query David Johnston <polobo@yahoo.com>
1 sibling, 2 replies; 30+ messages in thread
From: Roberto Scattini @ 2013-09-12 22:03 UTC (permalink / raw)
To: chris@chriscurvey.com; +Cc: pgsql-general
On Thu, Sep 12, 2013 at 6:49 PM, Chris Curvey <chris@chriscurvey.com> wrote:
>
>>
> Is this just a case-sentitvity issue? if personas.apellido is a varchar
> field, then I think that's your trouble. (it would have to be citext in
> order for "nunez" = "NUNEZ".)
>
>
>
yes, is just a case-sensitivity issue. yes, is a varchar field.
but what i dont understand is why the problem of "insensitivity" is only
with ñ (lower). i mean, if i remove my ñ (and subsecuent chars), both
querys return the same rows:
pgon=# set client_encoding = SQL_ASCII;
SET
pgon=# SELECT DISTINCT
p.id, p.apellido, p.nombre
FROM personal.personas AS p
WHERE p.apellido ilike '%NU%';
id | apellido | nombre
-------+----------------+------------------
39489 | NUÑEZ | JUAN
39937 | PEREZ NUÑEZ | FRANCISCO
40229 | PANNUNZIO | MARIA CRISTINA
39453 | GUARNUCIO | CARLA GISELLE
39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO
40326 | MANUZZA | ANDREA ALEJANDRA
40205 | NIETO FRAGNUL | LAUTARO ALFREDO
40220 | NUÑEZ VERA | MANUEL SANTIAGO
(8 rows)
pgon=# SELECT DISTINCT
p.id, p.apellido, p.nombre
FROM personal.personas AS p
WHERE p.apellido ilike '%nu%';
id | apellido | nombre
-------+----------------+------------------
39489 | NUÑEZ | JUAN
39937 | PEREZ NUÑEZ | FRANCISCO
40229 | PANNUNZIO | MARIA CRISTINA
39453 | GUARNUCIO | CARLA GISELLE
39498 | NUÑEZ ESQUIBEL | RICARDO RODOLFO
40326 | MANUZZA | ANDREA ALEJANDRA
40205 | NIETO FRAGNUL | LAUTARO ALFREDO
40220 | NUÑEZ VERA | MANUEL SANTIAGO
(8 rows)
what makes the 'ñ' char special that makes the queries the same when it is
not there?
--
Roberto Scattini
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 22:03 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
@ 2013-09-12 22:24 ` John R Pierce <pierce@hogranch.com>
1 sibling, 0 replies; 30+ messages in thread
From: John R Pierce @ 2013-09-12 22:24 UTC (permalink / raw)
To: pgsql-general
On 9/12/2013 3:03 PM, Roberto Scattini wrote:
> yes, is just a case-sensitivity issue. yes, is a varchar field.
> but what i dont understand is why the problem of "insensitivity" is
> only with ñ (lower). i mean, if i remove my ñ (and subsecuent chars),
> both querys return the same rows:
SQLASCII means its just 8 bit octets, and only standard USASCII
characters are recognized, which doesn't include ñ (what encoding is
that in, anyways?)
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Re: problem with query Chris Curvey <chris@chriscurvey.com>
2013-09-12 22:03 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
@ 2013-09-12 22:57 ` David Johnston <polobo@yahoo.com>
1 sibling, 0 replies; 30+ messages in thread
From: David Johnston @ 2013-09-12 22:57 UTC (permalink / raw)
To: pgsql-general
Roberto Scattini wrote
> what makes the 'ñ' char special that makes the queries the same when it is
> not there?
My knowledge here is a little rough around the edges but the following is
conceptually true:
For the most part legacy encodings (or non-encodings as this case
technically falls under) recognize only the explicit case-conversions for
the latin alphabet A-Z (mapped onto "a-z") without any accents.
To reasonably process strings/varchars/clobs that contain accented letters
it is necessary to use a more modern encoding - such as UTF-8/Unicode -
which contains the necessary logic to perform the additional conversions.
these should (not going to test it myself at this time) by
case-insensitively identical:
abcdëFGH
ABCDëfgh
since the "a-d, f-h" can be converted between and the one symbol that
cannot, "ë" is the same in both string....so it isn't that the ë breaks
things but rather that symbol has no upper-case alternative to equivalently
match against...just like numbers and symbols behave in the same situation.
To ASCII ë is just a symbol without any "letter of alphabet"
characteristics.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/problem-with-query-tp5770637p5770653.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
@ 2013-09-13 14:49 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-09-13 14:57 ` Re: problem with query Roberto Scattini <roberto.scattini@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Giuseppe Broccolo @ 2013-09-13 14:49 UTC (permalink / raw)
To: pgsql-general
Il 12/09/2013 22:34, Roberto Scattini ha scritto:
> hi, today we discovered that this query doesn't return the expected
> values:
>
> SELECT DISTINCT
> p.id <http://p.id;, p.apellido AS "Apellido", p.nombre AS "Nombre",
> pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/yyyy') AS
> "Fecha Ingreso PG", e.estado AS "Estado", to_char(pe.fecha,
> 'dd/mm/yyyy') AS "Fecha Estado"
> FROM personal.personas AS p
> LEFT JOIN personal.personas_da_pg AS da ON p.id
> <http://p.id>=da.id_persona
> LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id
> <http://pe.id;
> LEFT JOIN personal.estados AS e ON pe.id_estado=e.id <http://e.id;
> LEFT JOIN procu_departamento AS d ON d.id
> <http://d.id>=da.id_departamento
> LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama,
> 4)||'000'=dto.c_organigrama
> LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama,
> 3)||'0000'=dir.c_organigrama
> LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama,
> 2)||'00000'=dg.c_organigrama
> LEFT JOIN personal.funciones AS pf ON pf.id <http://pf.id>=da.funcion_id
> LEFT JOIN personal.profesiones AS pp ON pp.id
> <http://pp.id>=p.id_profesion
> WHERE p.apellido ilike '%nuñez%'
> ORDER BY "Apellido"
The problem is the encoding: SQL_ASCII encodes only the first 128
characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert
text 'ñ' or 'Ñ' in a table inside a database (e.g. 'ascii_test') with
SQL_ASCII encoding, they will be seen as two distinct characters:
ascii_test=# SELECT length(E'ñ');
length
--------
2
(1 row)
ascii_test=# SELECT length(E'Ñ');
length
--------
2
(1 row)
ascii_test=# SELECT 'ñ'::bytea;
bytea
--------
\xc3b1
(1 row)
ascii_test=# SELECT 'Ñ'::bytea;
bytea
--------
\xc391
(1 row)
Hexadecimals 'c3', 'b1' and '91' does not decode anything in ASCII, even
if terminal show pairs as 'ñ' or 'Ñ'. So ILIKE operator cannot apply
case insensitive pattern matching to data which does not encode any
string (in the SQL_ASCII encoding) and works as a normal LIKE.
Even if the client which insert data has 8-bit encoding (UTF8, where 'ñ'
and 'Ñ' are decoded), SQL_ASCII database cannot convert strings anyway,
and problem remains.
The best way is to work with a UTF8 encoded database. Is there a
particular reason to work with SQL_ASCII encoding?
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-13 14:49 ` Re: problem with query Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
@ 2013-09-13 14:57 ` Roberto Scattini <roberto.scattini@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Roberto Scattini @ 2013-09-13 14:57 UTC (permalink / raw)
To: pgsql-general
hi giuseppe,
On Fri, Sep 13, 2013 at 11:49 AM, Giuseppe Broccolo <
giuseppe.broccolo@2ndquadrant.it> wrote:
>
> The problem is the encoding: SQL_ASCII encodes only the first 128
> characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert text
> 'ñ' or 'Ñ' in a table inside a database (e.g. 'ascii_test') with SQL_ASCII
> encoding, they will be seen as two distinct characters:
>
>
> Hexadecimals 'c3', 'b1' and '91' does not decode anything in ASCII, even
> if terminal show pairs as 'ñ' or 'Ñ'. So ILIKE operator cannot apply case
> insensitive pattern matching to data which does not encode any string (in
> the SQL_ASCII encoding) and works as a normal LIKE.
>
> Even if the client which insert data has 8-bit encoding (UTF8, where 'ñ'
> and 'Ñ' are decoded), SQL_ASCII database cannot convert strings anyway, and
> problem remains.
>
> The best way is to work with a UTF8 encoded database. Is there a
> particular reason to work with SQL_ASCII encoding?
>
>
no, the only reason until now has been "If it ain't broke, don't fix it."...
so maybe it is time to start thinking in migrate the encoding to utf8...
is there any normal procedure to do this?
dump and then restore in a freshly initiated database should be enough?
thanks!
--
Roberto Scattini
^ permalink raw reply [nested|flat] 30+ messages in thread
* Problem with query
@ 2014-04-11 17:50 Susan Cassidy <susan.cassidy@decisionsciencescorp.com>
2014-04-11 18:24 ` Re: Problem with query David G Johnston <david.g.johnston@gmail.com>
2014-04-11 18:26 ` Re: Problem with query Chris Curvey <chris@chriscurvey.com>
0 siblings, 2 replies; 30+ messages in thread
From: Susan Cassidy @ 2014-04-11 17:50 UTC (permalink / raw)
To: pgsql-general
I have a query with several joins, where I am searching for specific data
in certain columns. If I do this:
SELECT distinct on (s.description, st1.description, s.scene_id)
s.description, s.scene_id, to_char(s.time_of_creation, 'MM/DD/YY
HH24:MI:SS'),
position_0_0_0_info, st.scene_thing_id, si.description,
st.description, m.description
from scenes s
left outer join scene_thing_instances si on s.scene_id = si.scene_id
left outer join scene_things st on si.scene_thing_id =
st.scene_thing_id
left outer join materials m on st.material_id = m.material_id
left outer join scene_things st1 on st.ultimate_parent_id =
st1.ultimate_parent_id
where st.description ilike '%bread%' or st1.description ilike
'%bread%'
group by s.description, st1.description, s.scene_id,
st.scene_thing_id, si.description, m.description order by s.description
No results are found, but if I just do this:
SELECT st.description, st1.description
from
scene_things st
left outer join scene_things st1 on st.ultimate_parent_id =
st1.ultimate_parent_id
where st1.description ilike '%bread%'
group by st.description, st1.description order by st.description
I get the results I expect (several hits).
What is the first query doing wrong?
I've tried adding st1.description to the SELECT list, and the GROUP BY
clause, with no luck.
Thanks,
Susan
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Problem with query
2014-04-11 17:50 Problem with query Susan Cassidy <susan.cassidy@decisionsciencescorp.com>
@ 2014-04-11 18:24 ` David G Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: David G Johnston @ 2014-04-11 18:24 UTC (permalink / raw)
To: pgsql-general
Susan Cassidy-3 wrote
> I have a query with several joins, where I am searching for specific data
> in certain columns.
While accurate as far as describing a typical query it doesn't really tell
us its intent....
> What is the first query doing wrong?
No idea, though it may have something to do with not properly handling NULL,
or, more likely, simply the "join order" semantics are wrong.
The first thing i would do is breakup the query into smaller components each
contained within their own CTE (WITH) section. I'd also minimize the number
of OUTER JOINs, it is doubtful that you need as many as you seem to think.
At best you should INNER JOIN first and then OUTER JOIN to the combination.
I also rarely require the use of "DISTINCT ON" if I've coded my queries
properly so removing that from the select-list would occur as well.
As would probably adding at least a "COALESCE(s.description,
st1.description)" somewhere.
And outputting "si.description" and "st.description" causes an ambiguity
since the resultant column name will not have the table prefix; typically
only the column name survives on the outside (or higher up).
> I've tried adding st1.description to the SELECT list, and the GROUP BY
> clause, with no luck.
And why did you think doing these things would have any impact on the number
of rows returned (or, at least, returning some rows where previously there
were none)?
David j.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-query-tp5799696p5799700.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Problem with query
2014-04-11 17:50 Problem with query Susan Cassidy <susan.cassidy@decisionsciencescorp.com>
@ 2014-04-11 18:26 ` Chris Curvey <chris@chriscurvey.com>
2014-04-11 20:48 ` Re: Problem with query Michael Nolan <htfoot@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Chris Curvey @ 2014-04-11 18:26 UTC (permalink / raw)
To: Susan Cassidy <susan.cassidy@decisionsciencescorp.com>; +Cc: pgsql-general
On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy <
susan.cassidy@decisionsciencescorp.com> wrote:
> I have a query with several joins, where I am searching for specific data
> in certain columns. If I do this:
>
> SELECT distinct on (s.description, st1.description, s.scene_id)
> s.description, s.scene_id, to_char(s.time_of_creation, 'MM/DD/YY
> HH24:MI:SS'),
> position_0_0_0_info, st.scene_thing_id, si.description,
> st.description, m.description
> from scenes s
> left outer join scene_thing_instances si on s.scene_id =
> si.scene_id
> left outer join scene_things st on si.scene_thing_id =
> st.scene_thing_id
> left outer join materials m on st.material_id = m.material_id
> left outer join scene_things st1 on st.ultimate_parent_id =
> st1.ultimate_parent_id
> where st.description ilike '%bread%' or st1.description ilike
> '%bread%'
> group by s.description, st1.description, s.scene_id,
> st.scene_thing_id, si.description, m.description order by s.description
>
> No results are found, but if I just do this:
>
> SELECT st.description, st1.description
> from
> scene_things st
> left outer join scene_things st1 on st.ultimate_parent_id =
> st1.ultimate_parent_id
> where st1.description ilike '%bread%'
> group by st.description, st1.description order by st.description
>
> I get the results I expect (several hits).
>
> What is the first query doing wrong?
>
> I've tried adding st1.description to the SELECT list, and the GROUP BY
> clause, with no luck.
>
> Thanks,
> Susan
>
First query goes
scenes -> scene_thing_instances -> scene_things
second query goes
scene_things -> scene_things
So they're not comparable queries.
My bet would be that scene_thing_instances is missing some rows that you
want/need.
--
I asked the Internet how to train my cat, and the Internet told me to get a
dog.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: Problem with query
2014-04-11 17:50 Problem with query Susan Cassidy <susan.cassidy@decisionsciencescorp.com>
2014-04-11 18:26 ` Re: Problem with query Chris Curvey <chris@chriscurvey.com>
@ 2014-04-11 20:48 ` Michael Nolan <htfoot@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Michael Nolan @ 2014-04-11 20:48 UTC (permalink / raw)
To: chris@chriscurvey.com; +Cc: Susan Cassidy <susan.cassidy@decisionsciencescorp.com>; pgsql-general
On 4/11/14, Chris Curvey <chris@chriscurvey.com> wrote:
> On Fri, Apr 11, 2014 at 1:50 PM, Susan Cassidy <
> susan.cassidy@decisionsciencescorp.com> wrote:
>
>> I have a query with several joins, where I am searching for specific data
>> in certain columns.
Have you tried running each of your joins separately to see if there
are row values common to both tables, ie:
select count(*) from scenes s
left outer join scene_thing_instances si on s.scene_id =
si.scene_id
then
select count(*) from scene_thing_instances si
left outer join scene_things st on si.scene_thing_id =
st.scene_thing_id
etc.
I find when building complex queries (I've written some that ran over
100 lines and involved a dozen or more joined tables), I need to build
them up, testing them as I build.
--
Mike Nolan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 30+ messages in thread
* problem with query
@ 2024-05-15 08:07 Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Sašo Gantar @ 2024-05-15 08:07 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
this query takes more than 8 seconds,
if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s
SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES
FROM
(WITH partitions AS
(SELECT array
(WITH partitioned_tables AS
(SELECT array
(SELECT oid
FROM pg_class
WHERE relkind = 'p') AS parent_tables) SELECT child.relname
AS PARTITION
FROM partitioned_tables, pg_inherits
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE
((nmsp_child.nspname='servicedesk'))
AND pg_inherits.inhparent = ANY
(partitioned_tables.parent_tables) ) AS NAMES) SELECT pgn.nspname AS
table_schema,
pgc.relname AS TABLE_NAME,
CASE
WHEN pgc.relkind = 'r' THEN 'TABLE'
WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'
WHEN pgc.relkind = 'v' THEN 'VIEW'
WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'
WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'
END AS table_type,
obj_description(pgc.oid) AS COMMENT,
COALESCE(json_agg(DISTINCT row_to_json(isc) :: JSONB
|| jsonb_build_object('comment', col_description(pga.attrelid,
pga.attnum))) filter (
WHERE isc.column_name IS NOT NULL), '[]' :: JSON) AS columns,
COALESCE(json_agg(DISTINCT row_to_json(ist) :: JSONB
|| jsonb_build_object('comment', obj_description(pgt.oid))) filter (
WHERE
ist.trigger_name IS NOT NULL), '[]' :: JSON) AS TRIGGERS,
row_to_json(isv) AS view_info
FROM partitions,
pg_class AS pgc
INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid /* columns
*/ /* This is a simplified version of how information_schema.columns was
** implemented in postgres 9.5, but modified to support materialized
** views. */
LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
a.attname AS COLUMN_NAME,
a.attnum AS ordinal_position,
pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
CASE
WHEN a.attnotnull
OR (t.typtype = 'd'
AND t.typnotnull) THEN 'NO'
ELSE 'YES'
END AS is_nullable,
CASE
WHEN t.typtype = 'd' THEN CASE
WHEN bt.typelem <> 0
AND bt.typlen = -1 THEN
'ARRAY'
WHEN nbt.nspname =
'pg_catalog' THEN format_type(t.typbasetype, NULL)
ELSE 'USER-DEFINED'
END
ELSE CASE
WHEN t.typelem <> 0
AND t.typlen = -1 THEN 'ARRAY'
WHEN nt.nspname = 'pg_catalog' THEN
format_type(a.atttypid, NULL)
ELSE 'USER-DEFINED'
END
END AS data_type,
coalesce(bt.typname, t.typname) AS data_type_name,
CASE
WHEN a.attidentity = 'd' THEN TRUE
WHEN a.attidentity = 'a' THEN TRUE
ELSE FALSE
END AS is_identity
FROM (pg_attribute a
LEFT JOIN pg_attrdef ad ON attrelid = adrelid
AND attnum = adnum)
JOIN (pg_class c
JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON
a.attrelid = c.oid
JOIN (pg_type t
JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON
a.atttypid = t.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON
(t.typtype = 'd'
AND t.typbasetype = bt.oid)
LEFT JOIN (pg_collation co
JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON
a.attcollation = co.oid
AND (nco.nspname,
co.collname) <> ('pg_catalog',
'default')
WHERE (NOT pg_is_other_temp_schema(nc.oid))
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relkind in ('r',
'v',
'm',
'f',
'p')
AND (pg_has_role(c.relowner, 'USAGE')
OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT,
UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname
AND isc.table_name = pgc.relname
AND isc.column_name = pga.attname /* triggers */
LEFT OUTER JOIN pg_trigger AS pgt ON pgt.tgrelid = pgc.oid
LEFT OUTER JOIN information_schema.triggers AS ist ON
ist.event_object_schema = pgn.nspname
AND ist.event_object_table = pgc.relname
AND ist.trigger_name = pgt.tgname /* This is a simplified version of how
information_schema.views was ** implemented in postgres 9.5, but
modified to support materialized ** views. */
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
CASE
WHEN pg_has_role(c.relowner, 'USAGE') THEN
pg_get_viewdef(c.oid)
ELSE NULL
END AS view_definition,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 20 = 20 THEN
'YES'
ELSE 'NO'
END AS is_updatable,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 8 = 8 THEN
'YES'
ELSE 'NO'
END AS is_insertable_into,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 81 = 81) THEN 'YES'
ELSE 'NO'
END AS is_trigger_updatable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 73 = 73) THEN 'YES'
ELSE 'NO'
END AS is_trigger_deletable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 69 = 69) THEN 'YES'
ELSE 'NO'
END AS is_trigger_insertable_into
FROM pg_namespace nc,
pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind in ('v',
'm')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE,
TRUNCATE, REFERENCES, TRIGGER')
OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE,
REFERENCES')) ) AS isv ON isv.table_schema = pgn.nspname
AND isv.table_name = pgc.relname
WHERE
pgc.relkind IN ('r',
'v',
'f',
'm',
'p')
AND ((pgn.nspname='servicedesk'))
GROUP BY pgc.oid,
pgn.nspname,
pgc.relname,
table_type,
isv.*) AS info;
Aggregate (cost=584.58..584.59 rows=1 width=32)
-> Subquery Scan on info (cost=578.68..584.54 rows=7 width=152)
-> GroupAggregate (cost=578.68..584.47 rows=7 width=324)
Group Key: pgc.oid, pgn.nspname, pgc.relname, (CASE WHEN
(pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind =
'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char")
THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED
VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text
ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN
pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE
NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass,
false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN
((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN
'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or
hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN
(alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE
'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6)
THEN 'YES'::text ELSE 'NO'::text END))
-> Sort (cost=578.68..578.70 rows=7 width=582)
Sort Key: pgc.oid, pgc.relname, (CASE WHEN (pgc.relkind
= 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::"char") THEN
'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char") THEN 'VIEW'::text
WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED VIEW'::text WHEN
(pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text ELSE NULL::text
END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner,
'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN
((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN
'YES'::text ELSE 'NO'::text END, CASE WHEN
((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN
'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or
hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN
(alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE
'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6)
THEN 'YES'::text ELSE 'NO'::text END))
-> Nested Loop Left Join (cost=390.47..578.58 rows=7
width=582)
Join Filter: ((nc_1.nspname = pgn.nspname) AND
(c_1.relname = pgc.relname))
-> Nested Loop Left Join (cost=349.13..469.16
rows=7 width=519)
Join Filter: (nc.nspname = pgn.nspname)
-> Nested Loop Left Join
(cost=346.35..441.12 rows=7 width=487)
-> Nested Loop Left Join
(cost=346.07..439.99 rows=1 width=417)
Join Filter:
(((ist.event_object_schema)::name = pgn.nspname) AND
((ist.event_object_table)::name = pgc.relname) AND
((ist.trigger_name)::name = pgt.tgname))
-> Nested Loop Left Join
(cost=41.62..81.99 rows=1 width=201)
-> Nested Loop
(cost=41.34..81.40 rows=1 width=133)
-> Index Scan
using pg_namespace_nspname_index on pg_namespace pgn (cost=0.28..2.49
rows=1 width=68)
Index Cond:
(nspname = 'servicedesk'::name)
-> Bitmap Heap
Scan on pg_class pgc (cost=41.07..78.76 rows=14 width=73)
Recheck Cond:
(relnamespace = pgn.oid)
Filter:
(relkind = ANY ('{r,v,f,m,p}'::"char"[]))
-> Bitmap
Index Scan on pg_class_relname_nsp_index (cost=0.00..41.06 rows=51 width=0)
Index
Cond: (relnamespace = pgn.oid)
-> Index Scan using
pg_trigger_tgrelid_tgname_index on pg_trigger pgt (cost=0.28..0.52 rows=7
width=72)
Index Cond:
(tgrelid = pgc.oid)
-> Subquery Scan on ist
(cost=304.45..357.95 rows=3 width=408)
Filter:
((ist.event_object_schema)::name = 'servicedesk'::name)
-> WindowAgg
(cost=304.45..351.43 rows=522 width=888)
-> Sort
(cost=304.45..305.75 rows=522 width=438)
Sort Key:
((n.nspname)::information_schema.sql_identifier),
((c_2.relname)::information_schema.sql_identifier), "*VALUES*".column1,
(((t_1.tgtype)::integer & 1)), (((t_1.tgtype)::integer & 66)), t_1.tgname
-> Nested
Loop (cost=192.35..280.88 rows=522 width=438)
Join
Filter: (((t_1.tgtype)::integer & "*VALUES*".column1) <> 0)
->
Hash Join (cost=192.35..265.12 rows=175 width=330)
Hash Cond: (t_1.tgrelid = c_2.oid)
-> Seq Scan on pg_trigger t_1 (cost=0.00..69.62 rows=374 width=202)
Filter: (NOT tgisinternal)
-> Hash (cost=178.52..178.52 rows=1106 width=136)
-> Hash Join (cost=22.81..178.52 rows=1106 width=136)
Hash Cond: (c_2.relnamespace = n.oid)
-> Seq Scan on pg_class c_2 (cost=0.00..151.32 rows=1659
width=76)
Filter: (pg_has_role(relowner, 'USAGE'::text) OR
has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES,
TRIGGER'::text) OR has_any_column_privilege(oid, 'INSERT, UPDATE,
REFERENCES'::text))
-> Hash (cost=17.29..17.29 rows=442 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..17.29 rows=442
width=68)
Filter: (NOT pg_is_other_temp_schema(oid))
->
Materialize (cost=0.00..0.05 rows=3 width=36)
-> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36)
-> Index Scan using
pg_attribute_relid_attnum_index on pg_attribute pga (cost=0.29..1.06
rows=7 width=70)
Index Cond: (attrelid = pgc.oid)
-> Nested Loop Left Join (cost=2.77..3.99
rows=1 width=224)
-> Nested Loop (cost=2.22..3.36
rows=1 width=228)
-> Nested Loop
(cost=1.95..3.03 rows=1 width=627)
-> Index Scan using
pg_class_relname_nsp_index on pg_class c (cost=0.28..0.44 rows=1 width=76)
Index Cond:
(relname = pgc.relname)
Filter: (relkind =
ANY ('{r,v,m,f,p}'::"char"[]))
-> Nested Loop
(cost=1.67..2.58 rows=1 width=571)
-> Nested Loop
Left Join (cost=1.39..2.25 rows=1 width=503)
-> Nested
Loop Left Join (cost=1.11..1.95 rows=1 width=294)
Join
Filter: (t.typtype = 'd'::"char")
->
Nested Loop (cost=0.56..1.31 rows=1 width=160)
-> Index Scan using pg_attribute_relid_attnam_index on pg_attribute a
(cost=0.29..1.01 rows=1 width=80)
Index Cond: ((attrelid = c.oid) AND (attname = pga.attname))
Filter: ((NOT attisdropped) AND (attnum > 0) AND
(pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid,
attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
-> Index Scan using pg_type_oid_index on pg_type t (cost=0.28..0.30
rows=1 width=84)
Index Cond: (oid = a.atttypid)
->
Nested Loop (cost=0.55..0.63 rows=1 width=138)
-> Index Scan using pg_type_oid_index on pg_type bt (cost=0.28..0.33
rows=1 width=78)
Index Cond: (oid = t.typbasetype)
-> Index Scan using pg_namespace_oid_index on pg_namespace nbt
(cost=0.28..0.30 rows=1 width=68)
Index Cond: (oid = bt.typnamespace)
-> Index
Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad
(cost=0.28..0.30 rows=1 width=211)
Index
Cond: ((adrelid = a.attrelid) AND (adnum = a.attnum))
-> Index Scan
using pg_namespace_oid_index on pg_namespace nc (cost=0.28..0.32 rows=1
width=68)
Index Cond:
(oid = c.relnamespace)
Filter: ((NOT
pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
-> Index Scan using
pg_namespace_oid_index on pg_namespace nt (cost=0.28..0.30 rows=1 width=68)
Index Cond: (oid =
t.typnamespace)
-> Nested Loop (cost=0.55..0.62
rows=1 width=4)
-> Index Scan using
pg_collation_oid_index on pg_collation co (cost=0.28..0.30 rows=1 width=72)
Index Cond: (oid =
a.attcollation)
-> Index Scan using
pg_namespace_oid_index on pg_namespace nco (cost=0.28..0.32 rows=1
width=68)
Index Cond: (oid =
co.collnamespace)
Filter: ((nspname <>
'pg_catalog'::name) OR (co.collname <> 'default'::name))
-> Materialize (cost=41.34..109.21 rows=1
width=160)
-> Nested Loop (cost=41.34..109.21 rows=1
width=160)
-> Index Scan using
pg_namespace_nspname_index on pg_namespace nc_1 (cost=0.28..2.50 rows=1
width=68)
Index Cond: (nspname =
'servicedesk'::name)
Filter: (NOT
pg_is_other_temp_schema(oid))
-> Bitmap Heap Scan on pg_class c_1
(cost=41.07..80.10 rows=2 width=76)
Recheck Cond: (relnamespace =
nc_1.oid)
Filter: ((relkind = ANY
('{v,m}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR
has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT,
INSERT, UPDATE, REFERENCES'::text)))
-> Bitmap Index Scan on
pg_class_relname_nsp_index (cost=0.00..41.06 rows=51 width=0)
Index Cond: (relnamespace
= nc_1.oid)
SubPlan 1
-> Index Scan using
pg_trigger_tgrelid_tgname_index on pg_trigger (cost=0.28..8.84 rows=1
width=0)
Index Cond: (tgrelid =
c_1.oid)
Filter: (((tgtype)::integer &
81) = 81)
SubPlan 2
-> Seq Scan on pg_trigger
pg_trigger_1 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer &
81) = 81)
SubPlan 3
-> Index Scan using
pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_2
(cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid =
c_1.oid)
Filter: (((tgtype)::integer &
73) = 73)
SubPlan 4
-> Seq Scan on pg_trigger
pg_trigger_3 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer &
73) = 73)
SubPlan 5
-> Index Scan using
pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_4
(cost=0.28..8.84 rows=1 width=0)
Index Cond: (tgrelid =
c_1.oid)
Filter: (((tgtype)::integer &
69) = 69)
SubPlan 6
-> Seq Scan on pg_trigger
pg_trigger_5 (cost=0.00..85.84 rows=11 width=4)
Filter: (((tgtype)::integer &
69) = 69)
PostgreSQL 13.14 (Ubuntu 13.14-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
@ 2024-05-15 11:23 ` David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-20 11:09 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
0 siblings, 2 replies; 30+ messages in thread
From: David Rowley @ 2024-05-15 11:23 UTC (permalink / raw)
To: Sašo Gantar <sasog23@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Wed, 15 May 2024 at 21:08, Sašo Gantar <sasog23@gmail.com> wrote:
> this query takes more than 8 seconds,
> if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s
Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't
very useful as there's no way to tell if the planner's estimates were
accurate or not. Also with EXPLAIN only, we don't know where the time
was spent in the query.
Running the EXPLAIN with "SET track_io_timing = 1;" would be even more useful.
David
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
@ 2024-05-20 11:08 ` Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 30+ messages in thread
From: Sašo Gantar @ 2024-05-20 11:08 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
>
>
> Aggregate (cost=512.53..512.54 rows=1 width=32) (actual
> time=8430.692..8430.724 rows=1 loops=1)
> Buffers: shared hit=2031540, temp read=954 written=956
> -> Subquery Scan on info (cost=510.85..512.52 rows=2 width=152)
> (actual time=8257.310..8430.532 rows=57 loops=1)
> Buffers: shared hit=2031540, temp read=954 written=956
> -> GroupAggregate (cost=510.85..512.50 rows=2 width=324) (actual
> time=8257.304..8430.427 rows=57 loops=1)
> Group Key: pgc.oid, pgn.nspname, pgc.relname, (CASE WHEN
> (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind =
> 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char")
> THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED
> VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text
> ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN
> pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE
> NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass,
> false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN
> ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN
> 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or
> hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN
> (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE
> 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6)
> THEN 'YES'::text ELSE 'NO'::text END))
> Buffers: shared hit=2031540, temp read=954 written=956
> -> Sort (cost=510.85..510.85 rows=2 width=582) (actual
> time=8253.824..8258.370 rows=12994 loops=1)
> Sort Key: pgc.oid, pgc.relname, (CASE WHEN
> (pgc.relkind = 'r'::"char") THEN 'TABLE'::text WHEN (pgc.relkind =
> 'f'::"char") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::"char")
> THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::"char") THEN 'MATERIALIZED
> VIEW'::text WHEN (pgc.relkind = 'p'::"char") THEN 'PARTITIONED TABLE'::text
> ELSE NULL::text END), (ROW(nc_1.nspname, c_1.relname, CASE WHEN
> pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE
> NULL::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass,
> false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN
> ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN
> 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or
> hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN
> (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE
> 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6)
> THEN 'YES'::text ELSE 'NO'::text END))
> Sort Method: external merge Disk: 7632kB
> Buffers: shared hit=2004085, temp read=954 written=956
> -> Nested Loop Left Join (cost=353.35..510.84 rows=2
> width=582) (actual time=25.558..8232.211 rows=12994 loops=1)
> Join Filter: (nc_1.nspname = pgn.nspname)
> Buffers: shared hit=2004074
> -> Nested Loop Left Join (cost=352.79..455.74
> rows=2 width=519) (actual time=25.548..8207.051 rows=12994 loops=1)
> Join Filter: (nc.nspname = pgn.nspname)
> Buffers: shared hit=1963631
> -> Nested Loop Left Join
> (cost=350.04..447.39 rows=2 width=487) (actual time=25.527..8049.285
> rows=12994 loops=1)
> Buffers: shared hit=1719586
> -> Nested Loop Left Join
> (cost=349.76..446.26 rows=1 width=417) (actual time=25.509..8039.922
> rows=429 loops=1)
> Join Filter:
> (((ist.event_object_schema)::name = pgn.nspname) AND
> ((ist.event_object_table)::name = pgc.relname) AND
> ((ist.trigger_name)::name = pgt.tgname))
> Rows Removed by Join Filter:
> 43600
> Buffers: shared hit=1711154
> -> Nested Loop Left Join
> (cost=39.75..81.15 rows=1 width=201) (actual time=0.209..1.186 rows=401
> loops=1)
> Buffers: shared hit=329
> -> Nested Loop
> (cost=39.47..80.56 rows=1 width=133) (actual time=0.179..0.475 rows=57
> loops=1)
> Buffers: shared
> hit=65
> -> Index Scan
> using pg_namespace_nspname_index on pg_namespace pgn (cost=0.28..2.49
> rows=1 width=68) (actual time=0.006..0.007 rows=1 loops=1)
> Index Cond:
> (nspname = 'servicedesk'::name)
> Buffers:
> shared hit=3
> -> Bitmap Heap
> Scan on pg_class pgc (cost=39.19..77.93 rows=14 width=73) (actual
> time=0.171..0.432 rows=57 loops=1)
> Recheck
> Cond: (relnamespace = pgn.oid)
> Filter:
> (relkind = ANY ('{r,v,f,m,p}'::"char"[]))
> Rows Removed
> by Filter: 163
> Heap Blocks:
> exact=44
> Buffers:
> shared hit=62
> -> Bitmap
> Index Scan on pg_class_relname_nsp_index (cost=0.00..39.19 rows=53
> width=0) (actual time=0.158..0.158 rows=220 loops=1)
> Index
> Cond: (relnamespace = pgn.oid)
>
> Buffers: shared hit=18
> -> Index Scan using
> pg_trigger_tgrelid_tgname_index on pg_trigger pgt (cost=0.28..0.52 rows=7
> width=72) (actual time=0.005..0.009 rows=7 loops=57)
> Index Cond:
> (tgrelid = pgc.oid)
> Buffers: shared
> hit=264
> -> Subquery Scan on ist
> (cost=310.01..365.05 rows=3 width=408) (actual time=11.344..20.034
> rows=109 loops=401)
> Filter:
> ((ist.event_object_schema)::name = 'servicedesk'::name)
> Rows Removed by Filter:
> 364
> Buffers: shared
> hit=1710825
> -> WindowAgg
> (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955
> rows=473 loops=401)
> Buffers: shared
> hit=1710825
> -> Sort
> (cost=310.01..311.35 rows=537 width=438) (actual time=0.008..0.042
> rows=473 loops=401)
> Sort Key:
> ((n.nspname)::information_schema.sql_identifier),
> ((c_2.relname)::information_schema.sql_identifier), "*VALUES*".column1,
> (((t_1.tgtype)::integer & 1)), (((t_1.tgtype)::integer & 66)), t_1.tgname
> Sort Method:
> quicksort Memory: 268kB
> Buffers:
> shared hit=2056
> -> Nested
> Loop (cost=195.66..285.66 rows=537 width=438) (actual time=2.143..2.752
> rows=473 loops=1)
> Join
> Filter: (((t_1.tgtype)::integer & "*VALUES*".column1) <> 0)
> Rows
> Removed by Join Filter: 679
>
> Buffers: shared hit=2053
> ->
> Hash Join (cost=195.66..269.45 rows=180 width=330) (actual
> time=2.136..2.532 rows=384 loops=1)
>
> Hash Cond: (t_1.tgrelid = c_2.oid)
>
> Buffers: shared hit=2053
>
> -> Seq Scan on pg_trigger t_1 (cost=0.00..70.55 rows=383 width=202)
> (actual time=0.005..0.313 rows=384 loops=1)
>
> Filter: (NOT tgisinternal)
>
> Rows Removed by Filter: 1872
>
> Buffers: shared hit=48
>
> -> Hash (cost=181.57..181.57 rows=1127 width=136) (actual
> time=2.120..2.123 rows=2401 loops=1)
>
> Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory
> Usage: 426kB
>
> Buffers: shared hit=2005
>
> -> Hash Join (cost=23.06..181.57 rows=1127 width=136) (actual
> time=0.892..1.695 rows=2401 loops=1)
>
> Hash Cond: (c_2.relnamespace = n.oid)
>
> Buffers: shared hit=2005
>
> -> Seq Scan on pg_class c_2 (cost=0.00..154.04 rows=1690
> width=76) (actual time=0.003..0.449 rows=2401 loops=1)
>
> Filter: (pg_has_role(relowner, 'USAGE'::text) OR
> has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES,
> TRIGGER'::text) OR has_any_column_privilege(oid, 'INSERT, UPDATE,
> REFERENCES'::text))
>
> Buffers: shared hit=58
>
> -> Hash (cost=17.44..17.44 rows=450 width=68) (actual
> time=0.868..0.869 rows=47 loops=1)
>
> Buckets: 1024 Batches: 1 Memory Usage: 13kB
>
> Buffers: shared hit=1947
>
> -> Seq Scan on pg_namespace n (cost=0.00..17.44
> rows=450 width=68) (actual time=0.003..0.859 rows=47 loops=1)
>
> Filter: (NOT pg_is_other_temp_schema(oid))
>
> Rows Removed by Filter: 642
>
> Buffers: shared hit=1947
> ->
> Materialize (cost=0.00..0.05 rows=3 width=36) (actual time=0.000..0.000
> rows=3 loops=384)
>
> -> Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36) (actual
> time=0.003..0.004 rows=3 loops=1)
> -> Index Scan using
> pg_attribute_relid_attnum_index on pg_attribute pga (cost=0.28..1.06
> rows=7 width=70) (actual time=0.005..0.016 rows=30 loops=429)
> Index Cond: (attrelid =
> pgc.oid)
> Buffers: shared hit=8432
> -> Nested Loop Left Join
> (cost=2.76..4.16 rows=1 width=224) (actual time=0.011..0.012 rows=1
> loops=12994)
> Buffers: shared hit=244045
> -> Nested Loop (cost=2.21..3.52
> rows=1 width=228) (actual time=0.010..0.011 rows=1 loops=12994)
> Buffers: shared hit=219133
> -> Nested Loop
> (cost=1.93..3.17 rows=1 width=627) (actual time=0.006..0.007 rows=1
> loops=12994)
> Buffers: shared
> hit=187599
> -> Index Scan using
> pg_class_relname_nsp_index on pg_class c (cost=0.28..0.44 rows=1 width=76)
> (actual time=0.001..0.001 rows=1 loops=12994)
> Index Cond:
> (relname = pgc.relname)
> Filter: (relkind =
> ANY ('{r,v,m,f,p}'::"char"[]))
> Buffers: shared
> hit=39712
> -> Nested Loop
> (cost=1.65..2.72 rows=1 width=571) (actual time=0.005..0.005 rows=1
> loops=13724)
> Buffers: shared
> hit=147887
> -> Nested Loop
> Left Join (cost=1.38..2.39 rows=1 width=503) (actual time=0.004..0.004
> rows=1 loops=13724)
> Buffers:
> shared hit=116864
> -> Nested
> Loop Left Join (cost=1.10..2.07 rows=1 width=294) (actual
> time=0.003..0.003 rows=1 loops=13724)
> Join
> Filter: (t.typtype = 'd'::"char")
>
> Buffers: shared hit=92576
> ->
> Nested Loop (cost=0.55..1.32 rows=1 width=160) (actual time=0.002..0.002
> rows=1 loops=13724)
>
> Buffers: shared hit=71866
>
> -> Index Scan using pg_attribute_relid_attnam_index on pg_attribute a
> (cost=0.28..1.00 rows=1 width=80) (actual time=0.001..0.001 rows=1
> loops=13724)
>
> Index Cond: ((attrelid = c.oid) AND (attname = pga.attname))
>
> Filter: ((NOT attisdropped) AND (attnum > 0) AND
> (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid,
> attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
>
> Rows Removed by Filter: 0
>
> Buffers: shared hit=40843
>
> -> Index Scan using pg_type_oid_index on pg_type t (cost=0.27..0.32
> rows=1 width=84) (actual time=0.001..0.001 rows=1 loops=10341)
>
> Index Cond: (oid = a.atttypid)
>
> Buffers: shared hit=31023
> ->
> Nested Loop (cost=0.55..0.74 rows=1 width=138) (actual time=0.001..0.001
> rows=0 loops=10341)
>
> Buffers: shared hit=20710
>
> -> Index Scan using pg_type_oid_index on pg_type bt (cost=0.27..0.42
> rows=1 width=78) (actual time=0.001..0.001 rows=0 loops=10341)
>
> Index Cond: (oid = t.typbasetype)
>
> Buffers: shared hit=20689
>
> -> Index Scan using pg_namespace_oid_index on pg_namespace nbt
> (cost=0.28..0.32 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=7)
>
> Index Cond: (oid = bt.typnamespace)
>
> Buffers: shared hit=21
> -> Index
> Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad
> (cost=0.28..0.32 rows=1 width=211) (actual time=0.001..0.001 rows=0
> loops=10341)
> Index
> Cond: ((adrelid = a.attrelid) AND (adnum = a.attnum))
>
> Buffers: shared hit=24288
> -> Index Scan
> using pg_namespace_oid_index on pg_namespace nc (cost=0.28..0.32 rows=1
> width=68) (actual time=0.001..0.001 rows=1 loops=10341)
> Index Cond:
> (oid = c.relnamespace)
> Filter:
> ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
> Rows Removed
> by Filter: 0
> Buffers:
> shared hit=31023
> -> Index Scan using
> pg_namespace_oid_index on pg_namespace nt (cost=0.28..0.32 rows=1
> width=68) (actual time=0.001..0.001 rows=1 loops=10150)
> Index Cond: (oid =
> t.typnamespace)
> Buffers: shared hit=30450
> -> Nested Loop (cost=0.55..0.63
> rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10150)
> Buffers: shared hit=24912
> -> Index Scan using
> pg_collation_oid_index on pg_collation co (cost=0.28..0.31 rows=1
> width=72) (actual time=0.001..0.001 rows=0 loops=10150)
> Index Cond: (oid =
> a.attcollation)
> Buffers: shared hit=21453
> -> Index Scan using
> pg_namespace_oid_index on pg_namespace nco (cost=0.28..0.32 rows=1
> width=68) (actual time=0.001..0.001 rows=0 loops=1153)
> Index Cond: (oid =
> co.collnamespace)
> Filter: ((nspname <>
> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
> Rows Removed by Filter: 1
> Buffers: shared hit=3459
> -> Nested Loop (cost=0.56..27.52 rows=1
> width=160) (actual time=0.001..0.001 rows=0 loops=12994)
> Buffers: shared hit=40443
> -> Index Scan using
> pg_class_relname_nsp_index on pg_class c_1 (cost=0.28..0.46 rows=1
> width=76) (actual time=0.001..0.001 rows=0 loops=12994)
> Index Cond: (relname = pgc.relname)
> Filter: ((relkind = ANY
> ('{v,m}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR
> has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE,
> REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT,
> INSERT, UPDATE, REFERENCES'::text)))
> Rows Removed by Filter: 1
> Buffers: shared hit=39712
> -> Index Scan using
> pg_namespace_oid_index on pg_namespace nc_1 (cost=0.28..0.43 rows=1
> width=68) (actual time=0.001..0.001 rows=1 loops=9)
> Index Cond: (oid = c_1.relnamespace)
> Filter: ((NOT
> pg_is_other_temp_schema(oid)) AND (nspname = 'servicedesk'::name))
> Buffers: shared hit=27
> SubPlan 1
> -> Index Scan using
> pg_trigger_tgrelid_tgname_index on pg_trigger (cost=0.28..8.80 rows=1
> width=0) (actual time=0.001..0.001 rows=0 loops=9)
> Index Cond: (tgrelid = c_1.oid)
> Filter: (((tgtype)::integer & 81)
> = 81)
> Buffers: shared hit=18
> SubPlan 2
> -> Seq Scan on pg_trigger pg_trigger_1
> (cost=0.00..87.46 rows=11 width=4) (never executed)
> Filter: (((tgtype)::integer & 81)
> = 81)
> SubPlan 3
> -> Index Scan using
> pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_2
> (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
> Index Cond: (tgrelid = c_1.oid)
> Filter: (((tgtype)::integer & 73)
> = 73)
> Buffers: shared hit=18
> SubPlan 4
> -> Seq Scan on pg_trigger pg_trigger_3
> (cost=0.00..87.46 rows=11 width=4) (never executed)
> Filter: (((tgtype)::integer & 73)
> = 73)
> SubPlan 5
> -> Index Scan using
> pg_trigger_tgrelid_tgname_index on pg_trigger pg_trigger_4
> (cost=0.28..8.80 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=9)
> Index Cond: (tgrelid = c_1.oid)
> Filter: (((tgtype)::integer & 69)
> = 69)
> Buffers: shared hit=18
> SubPlan 6
> -> Seq Scan on pg_trigger pg_trigger_5
> (cost=0.00..87.46 rows=11 width=4) (never executed)
> Filter: (((tgtype)::integer & 69)
> = 69)
> Planning:
> Buffers: shared hit=498
> Planning Time: 7.419 ms
> Execution Time: 8432.371 ms
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
@ 2024-05-21 10:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Laurenz Albe @ 2024-05-21 10:09 UTC (permalink / raw)
To: Sašo Gantar <sasog23@gmail.com>; David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote:
[execution plan without query text or explanation]
The time is lost here:
-> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual time=0.057..19.955 rows=473 loops=401)
Buffers: shared hit=1710825
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-05-21 10:49 ` Sašo Gantar <sasog23@gmail.com>
2024-05-21 11:14 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 14:18 ` Re: problem with query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 30+ messages in thread
From: Sašo Gantar @ 2024-05-21 10:49 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-general@lists.postgresql.org
thanks for the info, but is there any solution, given that it's system
tables?
regards
On Tue, 21 May 2024 at 12:09, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Mon, 2024-05-20 at 13:08 +0200, Sašo Gantar wrote:
> [execution plan without query text or explanation]
>
> The time is lost here:
>
> -> WindowAgg (cost=310.01..358.34 rows=537 width=888) (actual
> time=0.057..19.955 rows=473 loops=401)
> Buffers: shared hit=1710825
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
@ 2024-05-21 11:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 11:16 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-21 11:17 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
1 sibling, 2 replies; 30+ messages in thread
From: Laurenz Albe @ 2024-05-21 11:14 UTC (permalink / raw)
To: Sašo Gantar <sasog23@gmail.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-general@lists.postgresql.org
On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote:
> thanks for the info, but is there any solution, given that it's system tables?
We still don't know the query.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 11:14 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-05-21 11:16 ` David Rowley <dgrowleyml@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: David Rowley @ 2024-05-21 11:16 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Sašo Gantar <sasog23@gmail.com>; pgsql-general@lists.postgresql.org
On Tue, 21 May 2024 at 23:14, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> We still don't know the query.
hmm, it was posted on this thread:
https://postgr.es/m/CAGB0_6600w5C=hvhgfMWCqO9BCwCg+3s0PxXuoQv48NLqTp6dA@mail.gmail.com
David
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 11:14 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-05-21 11:17 ` Sašo Gantar <sasog23@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: Sašo Gantar @ 2024-05-21 11:17 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-general@lists.postgresql.org
sorry...
SELECT COALESCE(Json_agg(Row_to_json(info)), '[]' :: JSON) AS TABLES
FROM
(WITH partitions AS
(SELECT array
(WITH partitioned_tables AS
(SELECT array
(SELECT oid
FROM pg_class
WHERE relkind = 'p') AS parent_tables) SELECT child.relname
AS PARTITION
FROM partitioned_tables, pg_inherits
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE
((nmsp_child.nspname='servicedesk'))
AND pg_inherits.inhparent = ANY
(partitioned_tables.parent_tables) ) AS NAMES) SELECT pgn.nspname AS
table_schema,
pgc.relname AS TABLE_NAME,
CASE
WHEN pgc.relkind = 'r' THEN 'TABLE'
WHEN pgc.relkind = 'f' THEN 'FOREIGN TABLE'
WHEN pgc.relkind = 'v' THEN 'VIEW'
WHEN pgc.relkind = 'm' THEN 'MATERIALIZED VIEW'
WHEN pgc.relkind = 'p' THEN 'PARTITIONED TABLE'
END AS table_type,
obj_description(pgc.oid) AS COMMENT,
COALESCE(json_agg(DISTINCT row_to_json(isc) :: JSONB
|| jsonb_build_object('comment', col_description(pga.attrelid,
pga.attnum))) filter (
WHERE isc.column_name IS NOT NULL), '[]' :: JSON) AS columns,
COALESCE(json_agg(DISTINCT row_to_json(ist) :: JSONB
|| jsonb_build_object('comment', obj_description(pgt.oid))) filter (
WHERE
ist.trigger_name IS NOT NULL), '[]' :: JSON) AS TRIGGERS,
row_to_json(isv) AS view_info
FROM partitions,
pg_class AS pgc
INNER JOIN pg_namespace AS pgn ON pgc.relnamespace = pgn.oid /* columns
*/ /* This is a simplified version of how information_schema.columns was
** implemented in postgres 9.5, but modified to support materialized
** views. */
LEFT OUTER JOIN pg_attribute AS pga ON pga.attrelid = pgc.oid
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
a.attname AS COLUMN_NAME,
a.attnum AS ordinal_position,
pg_get_expr(ad.adbin, ad.adrelid) AS column_default,
CASE
WHEN a.attnotnull
OR (t.typtype = 'd'
AND t.typnotnull) THEN 'NO'
ELSE 'YES'
END AS is_nullable,
CASE
WHEN t.typtype = 'd' THEN CASE
WHEN bt.typelem <> 0
AND bt.typlen = -1 THEN
'ARRAY'
WHEN nbt.nspname =
'pg_catalog' THEN format_type(t.typbasetype, NULL)
ELSE 'USER-DEFINED'
END
ELSE CASE
WHEN t.typelem <> 0
AND t.typlen = -1 THEN 'ARRAY'
WHEN nt.nspname = 'pg_catalog' THEN
format_type(a.atttypid, NULL)
ELSE 'USER-DEFINED'
END
END AS data_type,
coalesce(bt.typname, t.typname) AS data_type_name,
CASE
WHEN a.attidentity = 'd' THEN TRUE
WHEN a.attidentity = 'a' THEN TRUE
ELSE FALSE
END AS is_identity
FROM (pg_attribute a
LEFT JOIN pg_attrdef ad ON attrelid = adrelid
AND attnum = adnum)
JOIN (pg_class c
JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON
a.attrelid = c.oid
JOIN (pg_type t
JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON
a.atttypid = t.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON
(t.typtype = 'd'
AND t.typbasetype = bt.oid)
LEFT JOIN (pg_collation co
JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON
a.attcollation = co.oid
AND (nco.nspname,
co.collname) <> ('pg_catalog',
'default')
WHERE (NOT pg_is_other_temp_schema(nc.oid))
AND a.attnum > 0
AND NOT a.attisdropped
AND c.relkind in ('r',
'v',
'm',
'f',
'p')
AND (pg_has_role(c.relowner, 'USAGE')
OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT,
UPDATE, REFERENCES')) ) AS isc ON isc.table_schema = pgn.nspname
AND isc.table_name = pgc.relname
AND isc.column_name = pga.attname /* triggers */
LEFT OUTER JOIN pg_trigger AS pgt ON pgt.tgrelid = pgc.oid
LEFT OUTER JOIN information_schema.triggers AS ist ON
ist.event_object_schema = pgn.nspname
AND ist.event_object_table = pgc.relname
AND ist.trigger_name = pgt.tgname /* This is a simplified version of how
information_schema.views was ** implemented in postgres 9.5, but
modified to support materialized ** views. */
LEFT OUTER JOIN
(SELECT nc.nspname AS table_schema,
c.relname AS TABLE_NAME,
CASE
WHEN pg_has_role(c.relowner, 'USAGE') THEN
pg_get_viewdef(c.oid)
ELSE NULL
END AS view_definition,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 20 = 20 THEN
'YES'
ELSE 'NO'
END AS is_updatable,
CASE
WHEN pg_relation_is_updatable(c.oid, FALSE) & 8 = 8 THEN
'YES'
ELSE 'NO'
END AS is_insertable_into,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 81 = 81) THEN 'YES'
ELSE 'NO'
END AS is_trigger_updatable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 73 = 73) THEN 'YES'
ELSE 'NO'
END AS is_trigger_deletable,
CASE
WHEN EXISTS
(SELECT 1
FROM pg_trigger
WHERE tgrelid = c.oid
AND tgtype & 69 = 69) THEN 'YES'
ELSE 'NO'
END AS is_trigger_insertable_into
FROM pg_namespace nc,
pg_class c
WHERE c.relnamespace = nc.oid
AND c.relkind in ('v',
'm')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND (pg_has_role(c.relowner, 'USAGE')
OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE,
TRUNCATE, REFERENCES, TRIGGER')
OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE,
REFERENCES')) ) AS isv ON isv.table_schema = pgn.nspname
AND isv.table_name = pgc.relname
WHERE
pgc.relkind IN ('r',
'v',
'f',
'm',
'p')
AND ((pgn.nspname='servicedesk'))
GROUP BY pgc.oid,
pgn.nspname,
pgc.relname,
table_type,
isv.*) AS info;
On Tue, 21 May 2024 at 13:14, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Tue, 2024-05-21 at 12:49 +0200, Sašo Gantar wrote:
> > thanks for the info, but is there any solution, given that it's system
> tables?
>
> We still don't know the query.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
@ 2024-05-21 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-22 04:04 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Tom Lane @ 2024-05-21 14:18 UTC (permalink / raw)
To: Sašo Gantar <sasog23@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; David Rowley <dgrowleyml@gmail.com>; pgsql-general@lists.postgresql.org
=?UTF-8?B?U2HFoW8gR2FudGFy?= <sasog23@gmail.com> writes:
> thanks for the info, but is there any solution, given that it's system
> tables?
Given the complexity of the query, I wonder if you're running into
problems with join_collapse_limit/from_collapse_limit preventing
the planner from considering all options.
Also, as David already mentioned, ANALYZE on pg_class might help.
regards, tom lane
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 14:18 ` Re: problem with query Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-05-22 04:04 ` Sašo Gantar <sasog23@gmail.com>
2024-05-27 11:39 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
0 siblings, 1 reply; 30+ messages in thread
From: Sašo Gantar @ 2024-05-22 04:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; David Rowley <dgrowleyml@gmail.com>; pgsql-general@lists.postgresql.org
ANALYZE pg_class; doesn't help
also, query is from "Hasura", so I don't have much room to maneuver
On Tue, 21 May 2024 at 16:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?B?U2HFoW8gR2FudGFy?= <sasog23@gmail.com> writes:
> > thanks for the info, but is there any solution, given that it's system
> > tables?
>
> Given the complexity of the query, I wonder if you're running into
> problems with join_collapse_limit/from_collapse_limit preventing
> the planner from considering all options.
>
> Also, as David already mentioned, ANALYZE on pg_class might help.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Re: problem with query Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-21 14:18 ` Re: problem with query Tom Lane <tgl@sss.pgh.pa.us>
2024-05-22 04:04 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
@ 2024-05-27 11:39 ` Sašo Gantar <sasog23@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: Sašo Gantar @ 2024-05-27 11:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; David Rowley <dgrowleyml@gmail.com>; pgsql-general@lists.postgresql.org
upgrade to "PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit"
solved the problem
regards,
s
On Wed, 22 May 2024 at 06:04, Sašo Gantar <sasog23@gmail.com> wrote:
> ANALYZE pg_class; doesn't help
> also, query is from "Hasura", so I don't have much room to maneuver
>
> On Tue, 21 May 2024 at 16:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> =?UTF-8?B?U2HFoW8gR2FudGFy?= <sasog23@gmail.com> writes:
>> > thanks for the info, but is there any solution, given that it's system
>> > tables?
>>
>> Given the complexity of the query, I wonder if you're running into
>> problems with join_collapse_limit/from_collapse_limit preventing
>> the planner from considering all options.
>>
>> Also, as David already mentioned, ANALYZE on pg_class might help.
>>
>> regards, tom lane
>>
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
@ 2024-05-20 11:09 ` Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:48 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Sašo Gantar @ 2024-05-20 11:09 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
what helps is
SET enable_nestloop = off;
query takes less then 2seconds
but it's probably not a good idea to change this flag
On Wed, 15 May 2024 at 13:23, David Rowley <dgrowleyml@gmail.com> wrote:
> On Wed, 15 May 2024 at 21:08, Sašo Gantar <sasog23@gmail.com> wrote:
> > this query takes more than 8 seconds,
> > if i remove "AND ((pgn.nspname='servicedesk'))" and test it, it takes <1s
>
> Including the EXPLAIN rather than EXPLAIN (ANALYZE, BUFFERS) isn't
> very useful as there's no way to tell if the planner's estimates were
> accurate or not. Also with EXPLAIN only, we don't know where the time
> was spent in the query.
>
> Running the EXPLAIN with "SET track_io_timing = 1;" would be even more
> useful.
>
> David
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: problem with query
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` Re: problem with query David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:09 ` Re: problem with query Sašo Gantar <sasog23@gmail.com>
@ 2024-05-21 10:48 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 30+ messages in thread
From: David Rowley @ 2024-05-21 10:48 UTC (permalink / raw)
To: Sašo Gantar <sasog23@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Mon, 20 May 2024 at 23:09, Sašo Gantar <sasog23@gmail.com> wrote:
> what helps is
> SET enable_nestloop = off;
> query takes less then 2seconds
> but it's probably not a good idea to change this flag
Looks like it's slow due to a bad selectivity estimate on the join
between pgn and pgc. This results in:
-> Nested Loop (cost=39.47..80.56 rows=1 width=133) (actual
time=0.179..0.475 rows=57 loops=1)
because the row estimate is 1, from there down to the root of the plan
tree the planner thinks Nested Loop is a good join type for the
remaining joins. Unfortunately, it's wrong.
I don't really see a good way to convince the planner not to do this.
The problem condition is:
Recheck Cond: (relnamespace = pgn.oid)
Filter: (relkind = ANY ('{r,v,f,m,p}'::"char"[]))
if ANALYZE pg_class; does not help then you could maybe mess with the
n_distinct estimate on pg_class.relnamespace, but you risk making
other queries worse.
Disabling enable_nestloop might be a good option, if you can just do
it for this query. Unfortunately, parameterized nested loops are also
tied into that GUC, so you'll stop those working for this plan. The
Nested Loop between pgn and pgc looks like a good choice. The rest,
not so much.
I don't think (ndistinct) extended statistics on pg_class
relnamespace, relkind will help since "relnamespace = pgn.oid" is a
join condition.
David
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2024-05-27 11:39 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-09-12 20:34 problem with query Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:22 ` Chris Curvey <chris@chriscurvey.com>
2013-09-12 21:33 ` Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 21:49 ` Chris Curvey <chris@chriscurvey.com>
2013-09-12 22:02 ` Bosco Rama <postgres@boscorama.com>
2013-09-12 22:07 ` Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 22:19 ` Rodrigo Gonzalez <rjgonzale.lists@gmail.com>
2013-09-12 22:22 ` Bosco Rama <postgres@boscorama.com>
2013-09-12 22:03 ` Roberto Scattini <roberto.scattini@gmail.com>
2013-09-12 22:24 ` John R Pierce <pierce@hogranch.com>
2013-09-12 22:57 ` David Johnston <polobo@yahoo.com>
2013-09-13 14:49 ` Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
2013-09-13 14:57 ` Roberto Scattini <roberto.scattini@gmail.com>
2014-04-11 17:50 Problem with query Susan Cassidy <susan.cassidy@decisionsciencescorp.com>
2014-04-11 18:24 ` Re: Problem with query David G Johnston <david.g.johnston@gmail.com>
2014-04-11 18:26 ` Re: Problem with query Chris Curvey <chris@chriscurvey.com>
2014-04-11 20:48 ` Re: Problem with query Michael Nolan <htfoot@gmail.com>
2024-05-15 08:07 problem with query Sašo Gantar <sasog23@gmail.com>
2024-05-15 11:23 ` David Rowley <dgrowleyml@gmail.com>
2024-05-20 11:08 ` Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:09 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 10:49 ` Sašo Gantar <sasog23@gmail.com>
2024-05-21 11:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-21 11:16 ` David Rowley <dgrowleyml@gmail.com>
2024-05-21 11:17 ` Sašo Gantar <sasog23@gmail.com>
2024-05-21 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-22 04:04 ` Sašo Gantar <sasog23@gmail.com>
2024-05-27 11:39 ` Sašo Gantar <sasog23@gmail.com>
2024-05-20 11:09 ` Sašo Gantar <sasog23@gmail.com>
2024-05-21 10:48 ` David Rowley <dgrowleyml@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox