Re: [GENERAL] Bad plan on a huge table query
Hi, I'm trying to figure out why does the planner found 1 row estimate using nested loops over a big table. There is no return from it: http://explain.depesz.com/s/GRs It returns if disable nested loops, but the plan still poor: http://explain.depesz.com/s/fMY I'm using PostgreSQL 9.2.3, default_statistics_target on 1000. I can't remember what to make PostgreSQL sees a better estimate in the scan of aula_confirmacao and the join with presenca. I got rusty after a long time just doing modeling. Does someone has some idea on that? Thanks, -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
[GENERAL] Problem in "Set search path"
Hi, I am using postgresql 9.0.3. In my application I change often schema name using set search path. Some times schema name set correctly. But some time it does not set correctly and it takes the schema previously I set. Is any possibilities to happen like this? Thank You.
Re: [GENERAL] Problem in "Set search path"
On Thu, Mar 21, 2013 at 11:33 PM, Kalai R wrote: > Hi, > >I am using postgresql 9.0.3. In my application I change often schema > name using set search path. Some times schema name set correctly. But some > time it does not set correctly and it takes the schema previously I set. Is > any possibilities to happen like this? If you roll back the transaction in which you set it, it isn't set, but if you commit that transaction, it'll stay set for future transactions. At least, that's been my experience. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem in "Set search path"
Hello 2013/3/21 Kalai R : > Hi, > >I am using postgresql 9.0.3. In my application I change often schema > name using set search path. Some times schema name set correctly. But some > time it does not set correctly and it takes the schema previously I set. Is > any possibilities to happen like this? yes, it is possible - if you use prepared statement. Regards Pavel Stehule > Thank You. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bad plan on a huge table query
I've reduced default_statistics_target to 200, and saw less nested loops. I'm trying some changes to the query, but no better results. The table presenca has 26 million rows. The table aula_confirmacao has 840 thousand rows. 2013/3/21 Daniel Cristian Cruz > Hi, > > I'm trying to figure out why does the planner found 1 row estimate using > nested loops over a big table. There is no return from it: > > http://explain.depesz.com/s/GRs > > It returns if disable nested loops, but the plan still poor: > > http://explain.depesz.com/s/fMY > > I'm using PostgreSQL 9.2.3, default_statistics_target on 1000. > > I can't remember what to make PostgreSQL sees a better estimate in the > scan of aula_confirmacao and the join with presenca. I got rusty after a > long time just doing modeling. > > Does someone has some idea on that? > > Thanks, > -- > Daniel Cristian Cruz > クルズ クリスチアン ダニエル > -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
Re: [GENERAL] State of the art re: group default privileges
On 03/20/2013 08:57 PM, Michael Orlitzky wrote: On 03/20/2013 08:05 PM, Adrian Klaver wrote: Not sure why everything being owned by dev_user is a problem, you said the developers don't care about permissions or want to deal with them so why does it matter what role their objects get created as? As long as developer roles inherit dev_user they get common access to the objects. I must have misspoken; things being owned by dev_user is not a problem. It's that, when we have 100 databases and I add a new developer, his permissions don't really kick in automatically. I have to go back and run a command on each database to which he should have access. Since I'm going to script it, it doesn't really matter /which/ commands I need to run. So it could be SET ROLE, or ALTER DEFAULT PRIVILEGES, or whatever else. But I shouldn't need to do any of it -- adding the user to the developers group should make him a developer (in all databases where that is meaningful), and that should be the end of it. The thing is roles are global to a cluster, they will be meaningful to all databases in the cluster. Imagine if, after adding yourself to the unix 'postgres' group, you had to go around and run a command on every file belonging to the 'postgres' group. And otherwise, you wouldn't be able to access those files. That would be weird, right? No one would want to do it, right? I don't want to do it in the database either =) Leave out the IN DATABASE and it will work for all databases in cluster. This won't fly unfortunately. It's a shared host, and the "developers" are a mixed bag of our employees, consultants, and the customer's employees. Do not follow. The set role= is put on a login role. It will only work on those databases the user role is allowed to log into. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to join table to itself N times?
Matt Wilson wrote > I got this table right now: > > select * from market_segment_dimension_values ; > +--+---+ > | market_segment_dimension | value | > +--+---+ > | geography| north | > | geography| south | > | industry type| retail| > | industry type| manufacturing | > | industry type| wholesale | > +--+---+ > (5 rows) Most likely you can solve your problem by using the "hstore" extension. I could be more certain of this if you actually state the requirements/use-case/business-problem. SQL requires that you know the column structure of the output so if hstore does not suffice you will have to execute a dynamic query in your API after querying the dimension map table to decide how many output columns you will need. hstore avoids that by giving you a dynamic table-in-a-column. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-join-table-to-itself-N-times-tp5749107p5749125.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
[GENERAL] Bad plan on a huge table query
Hi, I'm trying to figure out why does the planner found 1 row estimate using nested loops over a big table. There is no return from it: http://explain.depesz.com/s/GRs It returns if disable nested loops, but the plan still poor: http://explain.depesz.com/s/fMY I'm using PostgreSQL 9.2.3, default_statistics_target on 1000. I can't remember what to make PostgreSQL sees a better estimate in the scan of aula_confirmacao and the join with presenca. I got rusty after a long time just doing modeling. Does someone has some idea on that? Thanks, -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
Re: [GENERAL] State of the art re: group default privileges
On 03/21/2013 10:39 AM, Adrian Klaver wrote: >> >> This won't fly unfortunately. It's a shared host, and the "developers" >> are a mixed bag of our employees, consultants, and the customer's employees. > > Do not follow. The set role= is put on a login role. It will only work > on those databases the user role is allowed to log into. If one of our employees creates a table for one of our other projects, in one of our other databases, we don't want it being owned by a group of people who don't work for us. Or if we're working on a project for customer2, we don't want everything to be owned by the developers group if "developers" contains customer1's employees. (Not to mention: how would this work if we wanted to have two separate developers groups? I.e. if we had devs1 and devs2, with only some people in common.) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/21/2013 07:52 AM, Michael Orlitzky wrote: On 03/21/2013 10:39 AM, Adrian Klaver wrote: This won't fly unfortunately. It's a shared host, and the "developers" are a mixed bag of our employees, consultants, and the customer's employees. Do not follow. The set role= is put on a login role. It will only work on those databases the user role is allowed to log into. If one of our employees creates a table for one of our other projects, in one of our other databases, we don't want it being owned by a group of people who don't work for us. Or if we're working on a project for customer2, we don't want everything to be owned by the developers group if "developers" contains customer1's employees. I understand the above, I am just not sure how that differs from your file system analogy. Say: group dev users aklaver morlitzky Both users are made members of dev and granted access on each others files through dev. Any one else added to dev would have the same access, it would seem to be the same situation. Then you are led to the question below on different dev groups. Now it is entirely possible I am missing something obvious:) What it comes down to is the privileges system is what it is and while change is possible, probably not on a time scale that meets your immediate needs. Over the course of this conversation there have been quite a few scenerios presented, it might be helpful to create an outline of your usage cases and see what the collective wisdom comes up with. (Not to mention: how would this work if we wanted to have two separate developers groups? I.e. if we had devs1 and devs2, with only some people in common.) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] State of the art re: group default privileges
On 03/21/2013 11:34 AM, Adrian Klaver wrote: > On 03/21/2013 07:52 AM, Michael Orlitzky wrote: >> On 03/21/2013 10:39 AM, Adrian Klaver wrote: This won't fly unfortunately. It's a shared host, and the "developers" are a mixed bag of our employees, consultants, and the customer's employees. >>> >>> Do not follow. The set role= is put on a login role. It will only work >>> on those databases the user role is allowed to log into. >> >> If one of our employees creates a table for one of our other projects, >> in one of our other databases, we don't want it being owned by a group >> of people who don't work for us. >> >> Or if we're working on a project for customer2, we don't want everything >> to be owned by the developers group if "developers" contains customer1's >> employees. > > I understand the above, I am just not sure how that differs from your > file system analogy. Say: > > group > dev > users > aklaver > morlitzky > > Both users are made members of dev and granted access on each others > files through dev. Any one else added to dev would have the same access, > it would seem to be the same situation. Then you are led to the question > below on different dev groups. > When I add 'user3' to the 'dev' group above, he automatically gets access to everything that we have created. Likewise, if he creates something in a setgid 'dev' directory, then you and I could access it. This works instantly and without intervention even if we have 100 directories owned by 'dev' with a setgid bit. In postgres, it seems that I have to go back and either run SET ROLE or ALTER DEFAULT PRIVILEGES on each database. Otherwise, anything that 'user3' creates will be owned by 'user3', and you and I cannot touch it. > > What it comes down to is the privileges system is what it is and while > change is possible, probably not on a time scale that meets your > immediate needs. Over the course of this conversation there have been > quite a few scenerios presented, it might be helpful to create an > outline of your usage cases and see what the collective wisdom comes up > with. Alright, I'll step back and try to draft a minimal example that covers everything I want to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem in "Set search path"
Hi, Thanks for ur suggestions. I create connection from my .net application and set search path by single query. After set search path I call psql function from my .net application using the same connection. In this case some time search path set but some times not. I didn't use prepare statement. Regards kalai On Thu, Mar 21, 2013 at 6:09 PM, Pavel Stehule wrote: > Hello > > 2013/3/21 Kalai R : > > Hi, > > > >I am using postgresql 9.0.3. In my application I change often schema > > name using set search path. Some times schema name set correctly. But > some > > time it does not set correctly and it takes the schema previously I set. > Is > > any possibilities to happen like this? > > yes, it is possible - if you use prepared statement. > > Regards > > Pavel Stehule > > > Thank You. > > >
Re: [GENERAL] Problem in "Set search path"
On 03/21/2013 08:59 AM, Kalai R wrote: Hi, Thanks for ur suggestions. I create connection from my .net application and set search path by single query. After set search path I call psql function from my .net application using the same connection. In this case some time search path set but some times not. I didn't use prepare statement. Might try temporarily turning up the logging in postgresql.conf to 'all' and see what is actually being done on the server. Regards kalai -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem in "Set search path"
On 21 March 2013 16:59, Kalai R wrote: > Hi, > Thanks for ur suggestions. I create connection from my .net application > and set search path by single query. After set search path I call psql > function from my .net application using the same connection. In this case > some time search path set but some times not. I didn't use prepare > statement. > Is that the same connection object in ,NET or the same connection to a connection pool or the same database connection? That's not necessarily the same. Do you still have this problem if you set the search_path at the start of your transaction? Did you know you can set a search_path on user (role) objects and on database objects in the database? That might just take out your need to specify it in your ,NET application. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Re: [GENERAL] Bad plan on a huge table query
And now, it runs, at least: http://explain.depesz.com/s/GDJn No one could send a guess on it? 2013/3/21 Daniel Cristian Cruz > I've reduced default_statistics_target to 200, and saw less nested loops. > > I'm trying some changes to the query, but no better results. > > The table presenca has 26 million rows. The table aula_confirmacao has 840 > thousand rows. > > > 2013/3/21 Daniel Cristian Cruz > >> Hi, >> >> I'm trying to figure out why does the planner found 1 row estimate using >> nested loops over a big table. There is no return from it: >> >> http://explain.depesz.com/s/GRs >> >> It returns if disable nested loops, but the plan still poor: >> >> http://explain.depesz.com/s/fMY >> >> I'm using PostgreSQL 9.2.3, default_statistics_target on 1000. >> >> I can't remember what to make PostgreSQL sees a better estimate in the >> scan of aula_confirmacao and the join with presenca. I got rusty after a >> long time just doing modeling. >> >> Does someone has some idea on that? >> >> Thanks, >> -- >> Daniel Cristian Cruz >> クルズ クリスチアン ダニエル >> > > > > -- > Daniel Cristian Cruz > クルズ クリスチアン ダニエル > -- Daniel Cristian Cruz クルズ クリスチアン ダニエル
[GENERAL] streaming replication question
hi list, i'm installing a streaming replication master-slave setup in ubuntu 12.04 LTS, with postgresql 9.1 the tutorials and the documentation are a very good start point, but i have one question related to some fine grained configurations. it is said that i should stop the master db in order to make de starting backup, OR run pg_start_backup and keep the archive files for the slave to catch up. if i dont do this, maybe the slave stays far behind the master and the wal_segments arent enough to start the replication. if I understand this right, i can "combine" the old "warm standby" configs of archive_command in the master and restore command in the slave to ensure that the slave will have the necessary info to start the replication. i mean, i can have both configurations enabled? right now, my streaming replication setup has this configs: recovery.conf (slave) --- standby_mode = 'on' primary_conninfo = 'host=192.168.206.134' trigger_file = '/var/lib/postgresql/9.1/sgi/sgi.trigger' restore_command = '/usr/lib/postgresql/9.1/bin/pg_standby -d -k 100 -s 20 -t /var/lib/postgresql/9.1/sgi.trigger /var/lib/postgresql/9.1/archive %f %p' archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/archive %r' --- postgresql.conf (master) --- data_directory = '/var/lib/postgresql/9.1/sgi' hba_file = '/etc/postgresql/9.1/sgi/pg_hba.conf' ident_file = '/etc/postgresql/9.1/sgi/pg_ident.conf' external_pid_file = '/var/run/postgresql/9.1-sgi.pid' listen_addresses = '*' port = 5432 max_connections = 100 unix_socket_directory = '/var/run/postgresql' ssl = true shared_buffers = 24MB wal_level = hot_standby archive_mode = on archive_command = 'rsync -arv %p 192.168.206.133:/var/lib/postgresql/9.1/archive/%f
Re: [GENERAL] streaming replication question
On Thu, Mar 21, 2013 at 3:32 PM, Roberto Scattini < roberto.scatt...@gmail.com> wrote: > hi list, > > i'm installing a streaming replication master-slave setup in ubuntu 12.04 > LTS, with postgresql 9.1 > > the tutorials and the documentation are a very good start point, but i > have one question related to some fine grained configurations. > > it is said that i should stop the master db in order to make de starting > backup, OR run pg_start_backup and keep the archive files for the slave to > catch up. if i dont do this, maybe the slave stays far behind the master > and the wal_segments arent enough to start the replication. > if I understand this right, i can "combine" the old "warm standby" configs > of archive_command in the master and restore command in the slave to ensure > that the slave will have the necessary info to start the replication. > > > i mean, i can have both configurations enabled? > > no, i think my setup, with this config, is just doing warm standby. but the streaming replication was working before adding archive_command to master and restore_command to slave. i used the info from this site: http://wiki.postgresql.org/wiki/Streaming_Replication -- Roberto Scattini
Re: [GENERAL] Bad plan on a huge table query
On Mar 20, 2013, at 22:36, Daniel Cristian Cruz wrote: > Hi, > > I'm trying to figure out why does the planner found 1 row estimate using > nested loops over a big table. There is no return from it: > > http://explain.depesz.com/s/GRs That plan contains no actual statistics, which makes it difficult to say anything about it. And you didn't provide any info on table definitions or indexes whatsoever, we have to put that together from the generated query plans. Not great... > It returns if disable nested loops, but the plan still poor: > > http://explain.depesz.com/s/fMY You could probably gain some here by adding an index on aluno_mensal.id_medicao. In step 14 the lack thereof causes a seqscan over more than a million rows. What I also find a bit peculiar is that the filter in step 7 appears to apply a function (date_part(text, date)) on every row in that heap. Do you perhaps have a functional index on that table that makes that operation efficient? Besides, now() results in a timestamp, which in this query needs to be converted to date; it's perhaps better to use CURRENT_DATE there, although the benefits are probably immeasurable since it only needs to be calculated once for all rows it's compared against. > I'm using PostgreSQL 9.2.3, default_statistics_target on 1000. > > I can't remember what to make PostgreSQL sees a better estimate in the scan > of aula_confirmacao and the join with presenca. I got rusty after a long time > just doing modeling. > > Does someone has some idea on that? Are you asking about vacuum? You're definitely not vacuuming enough, your statistics and your actual numbers of rows differ by enormous amounts (that's why depesz colours them red). Are you using autovacuum? If so, you probably need to tune it more aggressively. For the short term, running an ANALYSE on those tables should at least get you more accurate query plans. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
Re: [GENERAL] streaming replication question
On Thu, Mar 21, 2013 at 3:45 PM, Roberto Scattini < roberto.scatt...@gmail.com> wrote: > > i mean, i can have both configurations enabled? >> >> > no, i think my setup, with this config, is just doing warm standby. but > the streaming replication was working before adding archive_command to > master and restore_command to slave. > > if i remove restore_command and archive_cleanup_command from recovery.conf on slave the streaming replication works again. so, i cant combine both methods? i must shut down my master to start the streaming replication? this comment confused me: # Specifies a command to load archive segments from the WAL archive. If # wal_keep_segments is a high enough number to retain the WAL segments # required for the standby server, this may not be necessary. But # a large workload can cause segments to be recycled before the standby # is fully synchronized, requiring you to start again from a new base backup. restore_command = 'cp /path_to/archive/%f "%p"' -- Roberto Scattini
Re: [GENERAL] Bad plan on a huge table query
On Thu, Mar 21, 2013 at 10:53 AM, Daniel Cristian Cruz <
danielcrist...@gmail.com> wrote:
> And now, it runs, at least:
>
> http://explain.depesz.com/s/GDJn
>
> No one could send a guess on it?
>
Without showing either the query, or the definition of the tables, you are
not giving us much to go on.
I'm particularly interested in what inicio is and what
idx_aula_confirmacao_2 is. If inicio is already a date, why is being cast
to date? And if it is not already a date, how can it be used in the index
scan?
-> Index Scan using idx_aula_confirmacao_2 on aula_confirmacao
(cost=0.01..7582.88 rows=4442 width=24) (actual time=64.017..81747.794
rows=101508 loops=1)"
Index Cond: (((inicio)::date >=
date_trunc('YEAR'::text, now())) AND ((inicio)::date <= now()))"
Cheers,
Jeff
Re: [GENERAL] Bad plan on a huge table query
2013/3/21 Alban Hertroys
> On Mar 20, 2013, at 22:36, Daniel Cristian Cruz
> wrote:
>
> Hi,
>
> I'm trying to figure out why does the planner found 1 row estimate using
> nested loops over a big table. There is no return from it:
>
> http://explain.depesz.com/s/GRs
>
>
> That plan contains no actual statistics, which makes it difficult to say
> anything about it. And you didn't provide any info on table definitions or
> indexes whatsoever, we have to put that together from the generated query
> plans. Not great...
>
My bad... I guess the plan could do it. And now I figured out that I lost
the first query... Now the query looks like this:
> It returns if disable nested loops, but the plan still poor:
>
> http://explain.depesz.com/s/fMY
>
>
> You could probably gain some here by adding an index on
> aluno_mensal.id_medicao. In step 14 the lack thereof causes a seqscan over
> more than a million rows.
>
There is already an index on id_medicao. It used a hashjoin because I
disable mergejoin which uses the index, instead there is no return.
> What I also find a bit peculiar is that the filter in step 7 appears to
> apply a function (date_part(text, date)) on every row in that heap. Do you
> perhaps have a functional index on that table that makes that operation
> efficient?
>
Yes, tried to improve performance creating a index on inicio using
CAST(inicio TO DATE). The real filter here is aula_confirmacao.inicio::date
BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
(filtering all rows from year's beginning until now).
> Besides, now() results in a timestamp, which in this query needs to be
> converted to date; it's perhaps better to use CURRENT_DATE there, although
> the benefits are probably immeasurable since it only needs to be calculated
> once for all rows it's compared against.
>
DATE_TRUNC expect a text and a timestamp.
>
> I'm using PostgreSQL 9.2.3, default_statistics_target on 1000.
>
> I can't remember what to make PostgreSQL sees a better estimate in the
> scan of aula_confirmacao and the join with presenca. I got rusty after a
> long time just doing modeling.
>
> Does someone has some idea on that?
>
>
> Are you asking about vacuum? You're definitely not vacuuming enough, your
> statistics and your actual numbers of rows differ by enormous amounts
> (that's why depesz colours them red).
>
autovacuum is running on production and the develop database. This is
happening at develop database, fresh restore.
> Are you using autovacuum? If so, you probably need to tune it more
> aggressively. For the short term, running an ANALYSE on those tables should
> at least get you more accurate query plans.
>
I've done it; with default_statistics_target on 1000, 100 and 200 (left it
on 200, which was production config too).
Thank you and sorry about the broken english, there was a long time since
the last time I wrote...
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
Re: [GENERAL] Bad plan on a huge table query
Ooops, no query... Now it goes (Jeff, types in each line):
SELECT
aluno_mensal.id_matricula, --integer
aluno_mensal.id_turma, --integer
aluno_mensal.turma, --text
aluno_mensal.id_produto_educacao, --integer
aluno_mensal.produto_educacao, --text
aluno_mensal.unidade, --text
aluno_mensal.unidade_execucao, --text
aluno_mensal.modalidade, --text
aluno_mensal.id_pessoa, --integer
aluno_mensal.nome_pessoa, --text
presenca.id_diario, --integer
aula_confirmacao.inicio::date AS data_aula, --timestamp to date
presenca.justificativa_falta, --text
SUM(aula_confirmacao.termino - aula_confirmacao.inicio) AS carga_faltas,
--interval
mensal.ano AS ano_apuracao, --integer
mensal.mes AS mes_apuracao --integer
FROM indicadores.aluno_mensal
JOIN indicadores.mensal
ON mensal.id_mensal = aluno_mensal.id_mensal
JOIN turma.presenca
ON presenca.id_matricula = aluno_mensal.id_matricula
JOIN turma.aula_confirmacao
ON aula_confirmacao.id_evento = presenca.id_evento
JOIN turma.estudante_periodo
ON
estudante_periodo.id_matricula = presenca.id_matricula AND
estudante_periodo.id_diario = presenca.id_diario AND
aula_confirmacao.inicio::date BETWEEN estudante_periodo.inicio AND
estudante_periodo.termino -- timestamp, date, date
WHERE
presenca.inicio::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND
CURRENT_TIMESTAMP AND -- timestamp
NOT presenca.presente AND --boolean
mensal.ano = EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AND -- integer
aula_confirmacao.inicio::DATE BETWEEN DATE_TRUNC('YEAR',
CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP AND --timestamp to date
aula_confirmacao.confirmada AND -- boolean
aluno_mensal.id_medicao = 7
GROUP BY
aluno_mensal.id_matricula,
aluno_mensal.id_turma,
aluno_mensal.turma,
aluno_mensal.id_produto_educacao,
aluno_mensal.produto_educacao,
aluno_mensal.unidade,
aluno_mensal.unidade_execucao,
aluno_mensal.modalidade,
aluno_mensal.id_pessoa,
aluno_mensal.nome_pessoa,
presenca.id_diario,
aula_confirmacao.inicio::date,
presenca.justificativa_falta,
mensal.ano,
mensal.mes;
2013/3/21 Daniel Cristian Cruz
> 2013/3/21 Alban Hertroys
>
>> On Mar 20, 2013, at 22:36, Daniel Cristian Cruz
>> wrote:
>>
>> Hi,
>>
>> I'm trying to figure out why does the planner found 1 row estimate using
>> nested loops over a big table. There is no return from it:
>>
>> http://explain.depesz.com/s/GRs
>>
>>
>> That plan contains no actual statistics, which makes it difficult to say
>> anything about it. And you didn't provide any info on table definitions or
>> indexes whatsoever, we have to put that together from the generated query
>> plans. Not great...
>>
>
> My bad... I guess the plan could do it. And now I figured out that I lost
> the first query... Now the query looks like this:
>
>
>
>
>> It returns if disable nested loops, but the plan still poor:
>>
>> http://explain.depesz.com/s/fMY
>>
>>
>> You could probably gain some here by adding an index on
>> aluno_mensal.id_medicao. In step 14 the lack thereof causes a seqscan over
>> more than a million rows.
>>
>
> There is already an index on id_medicao. It used a hashjoin because I
> disable mergejoin which uses the index, instead there is no return.
>
>
>> What I also find a bit peculiar is that the filter in step 7 appears to
>> apply a function (date_part(text, date)) on every row in that heap. Do you
>> perhaps have a functional index on that table that makes that operation
>> efficient?
>>
>
> Yes, tried to improve performance creating a index on inicio using
> CAST(inicio TO DATE). The real filter here is aula_confirmacao.inicio::date
> BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
> (filtering all rows from year's beginning until now).
>
>
>> Besides, now() results in a timestamp, which in this query needs to be
>> converted to date; it's perhaps better to use CURRENT_DATE there, although
>> the benefits are probably immeasurable since it only needs to be calculated
>> once for all rows it's compared against.
>>
>
> DATE_TRUNC expect a text and a timestamp.
>
>
>>
>> I'm using PostgreSQL 9.2.3, default_statistics_target on 1000.
>>
>> I can't remember what to make PostgreSQL sees a better estimate in the
>> scan of aula_confirmacao and the join with presenca. I got rusty after a
>> long time just doing modeling.
>>
>> Does someone has some idea on that?
>>
>>
>> Are you asking about vacuum? You're definitely not vacuuming enough, your
>> statistics and your actual numbers of rows differ by enormous amounts
>> (that's why depesz colours them red).
>>
>
> autovacuum is running on production and the develop database. This is
> happening at develop database, fresh restore.
>
>
>> Are you using autovacuum? If so, you probably need to tune it more
>> aggressively. For the short term, running an ANALYSE on those tables should
>> at least get you more accurate query plans.
>>
>
> I've done it; with default_statistics_target on 1000, 100 and 200 (left it
> on 200, which was production config too).
>
> Thank yo
Re: [GENERAL] regexp_replace failing on 9.0.4
On 03/18/2013 02:40 PM, Tom Lane wrote: Rob Sargent writes: On 03/18/2013 01:19 PM, Tom Lane wrote: Rob Sargent writes: On our 9.0.4[1] server my regexp_replace is a no-op, but on the 9.0.3[2] test machine and my 9.1.2[3] dev box all is fine AFAICS from the commit logs, there were no changes affecting the regex code between 9.0.3 and 9.0.4. I'm suspicious that your data is different on the different servers. Good to hear, thought I might have glossed over the telling release note - my usual mo Maybe we're barking up the wrong tree by suspecting the regex itself. Perhaps the updates were suppressed by a trigger, or the transaction rolled back instead of committing, or some such? regards, tom lane For fun I decided to install 9.2 and thought I would try my luck there. Here's was I saw (apologies for the wide output). #localhost:cms# select count(*) from pg_trigger; +---+ | count | +---+ | 364 | +---+ (1 row) Time: 0.407 ms #localhost:cms# select tgname from pg_trigger where tgname !~ '^RI_ConstraintTrigger'; ++ | tgname | ++ ++ (0 rows) #localhost:cms# select version(); +---+ | version | +- | PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit | +---+ (1 row) select substring(text,1,150) from cms.segment_data where id = 'c092880f-8484-4b29-b712-f3df12216701'; ++ | substring | ++ | \r +| | http://amirsys.com/ns/acres/pathology/dx/1.5"; enable-tables="true">diversion_c | ++ (1 row) #localhost:cms# begin; BEGIN << simple update in place>> update cms.segment_data set text = regexp_replace(text,'(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') where id = 'c092880f-8484-4b29-b712-f3df12216701'; UPDATE 1 < check >> select substring(text,1,150) from cms.segment_data where id = 'c092880f-8484-4b29-b712-f3df12216701'; ++ | substring | ++ | \r +| | http://amirsys.com/ns/acres/pathology/dx/1.5"; enable-tables="true">diversion_c | ++ (1 row) NO CHANGE (still "1.5"); << update in parts >> update cms.segment_data set text = regexp_replace(substring(text,1,150), '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') || substring(text, 151) where id = 'c092880f-8484-4b29-b712-f3df12216701'; UPDATE 1 <> select substring(text,1,150) from cms.segment_data where id = 'c092880f-8484-4b29-b712-f3df12216701'; ++ | substring | ++ | \r +| | http://amirsys.com/ns/acres/pathology/dx/1.6"; enable-tables="true">diversion_c | ++ (1 row) <> ROLLBACK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bad plan on a huge table query
On Thu, Mar 21, 2013 at 12:30 PM, Daniel Cristian Cruz <
danielcrist...@gmail.com> wrote:
>
>
>> Are you using autovacuum? If so, you probably need to tune it more
>> aggressively. For the short term, running an ANALYSE on those tables should
>> at least get you more accurate query plans.
>>
>
> I've done it; with default_statistics_target on 1000, 100 and 200 (left it
> on 200, which was production config too).
>
You are doing an manual analyze each time you change
default_statistics_target, right?
Can you do an "analyze verbose aula_confirmacao" and see if the output is
as expected?
what happens if you do:
explain (analyze, buffers)
select count(*) from aula_confirmacao where
inicio::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND
CURRENT_TIMESTAMP;
>From your explain plan http://explain.depesz.com/s/GDJn, step 9, the row
estimates for that simple query should be off by a factor of
23 (101508/4442), yet there is no apparent reason for that to give a bad
estimate, other than bad statistics. There are no filters so cross-column
correlations can't be throwing it off, so why is it so bad?
Also, it would be nice to see:
select * from pg_stats where tablename = 'idx_aula_confirmacao_2' \x\g\x
(which I assume is a function-based index)
Cheers,
Jeff
Re: [GENERAL] regexp_replace failing on 9.0.4
Rob Sargent writes: > For fun I decided to install 9.2 and thought I would try my luck there. > Here's was I saw (apologies for the wide output). > << simple update in place>> > update cms.segment_data > set text = regexp_replace(text,'(^.*)ns/acres/pathology/dx/1.5(.*$)', > E'\\1ns/acres/pathology/dx/1.6\\2') > where id = 'c092880f-8484-4b29-b712-f3df12216701'; > UPDATE 1 > [ doesn't change the first 150 characters of the field ] > << update in parts >> > update cms.segment_data set text = > regexp_replace(substring(text,1,150), > '(^.*)ns/acres/pathology/dx/1.5(.*$)', > E'\\1ns/acres/pathology/dx/1.6\\2') || substring(text, 151) > where id = 'c092880f-8484-4b29-b712-f3df12216701'; > UPDATE 1 > [ does change the first 150 characters of the field ] I'm suspicious that there is more than one match to the substring in that field, with the extra match(es) coming beyond char 150. The way that regexp is written, I think it would replace the last match not the first. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp_replace failing on 9.0.4
On 03/21/2013 06:25 PM, Tom Lane wrote: Rob Sargent writes: For fun I decided to install 9.2 and thought I would try my luck there. Here's was I saw (apologies for the wide output). << simple update in place>> update cms.segment_data set text = regexp_replace(text,'(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') where id = 'c092880f-8484-4b29-b712-f3df12216701'; UPDATE 1 [ doesn't change the first 150 characters of the field ] << update in parts >> update cms.segment_data set text = regexp_replace(substring(text,1,150), '(^.*)ns/acres/pathology/dx/1.5(.*$)', E'\\1ns/acres/pathology/dx/1.6\\2') || substring(text, 151) where id = 'c092880f-8484-4b29-b712-f3df12216701'; UPDATE 1 [ does change the first 150 characters of the field ] I'm suspicious that there is more than one match to the substring in that field, with the extra match(es) coming beyond char 150. The way that regexp is written, I think it would replace the last match not the first. regards, tom lane Indeed there are (or at least may be) other instances of the namespace string. I was operating on the assumption that only the first would get hit, but I fear greediness has gotten the better of my yet again. And there's reason to believe the "9.0.3" test server db did not have the proliferations of the string. Thanks as always. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication question
You can combine warm standby and streaming, we do this in our product database. When the standby is to far from the master, the slave will try to use restore_command to restore the database(warm standby), when the standby catch up the master, the steaming will working again. BTW: we use ominipitr. On Fri, Mar 22, 2013 at 2:32 AM, Roberto Scattini < roberto.scatt...@gmail.com> wrote: > hi list, > > i'm installing a streaming replication master-slave setup in ubuntu 12.04 > LTS, with postgresql 9.1 > > the tutorials and the documentation are a very good start point, but i > have one question related to some fine grained configurations. > > it is said that i should stop the master db in order to make de starting > backup, OR run pg_start_backup and keep the archive files for the slave to > catch up. if i dont do this, maybe the slave stays far behind the master > and the wal_segments arent enough to start the replication. > if I understand this right, i can "combine" the old "warm standby" configs > of archive_command in the master and restore command in the slave to ensure > that the slave will have the necessary info to start the replication. > > > i mean, i can have both configurations enabled? > right now, my streaming replication setup has this configs: > > recovery.conf (slave) > --- > standby_mode = 'on' > primary_conninfo = 'host=192.168.206.134' > trigger_file = '/var/lib/postgresql/9.1/sgi/sgi.trigger' > restore_command = '/usr/lib/postgresql/9.1/bin/pg_standby -d -k 100 -s 20 > -t /var/lib/postgresql/9.1/sgi.trigger /var/lib/postgresql/9.1/archive %f > %p' > archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup > /var/lib/postgresql/9.1/archive %r' > --- > > postgresql.conf (master) > --- > data_directory = '/var/lib/postgresql/9.1/sgi' > hba_file = '/etc/postgresql/9.1/sgi/pg_hba.conf' > ident_file = '/etc/postgresql/9.1/sgi/pg_ident.conf' > external_pid_file = '/var/run/postgresql/9.1-sgi.pid' > listen_addresses = '*' > port = 5432 > max_connections = 100 > unix_socket_directory = '/var/run/postgresql' > ssl = true > shared_buffers = 24MB > wal_level = hot_standby > archive_mode = on > archive_command = 'rsync -arv %p > 192.168.206.133:/var/lib/postgresql/9.1/archive/%f > archive_timeout = 900 > max_wal_senders = 5 > wal_keep_segments = 100 > log_line_prefix = '%t ' > datestyle = 'iso, mdy' > lc_messages = 'en_US.UTF-8' > lc_monetary = 'en_US.UTF-8' > lc_numeric = 'en_US.UTF-8' > lc_time = 'en_US.UTF-8' > default_text_search_config = 'pg_catalog.english' > --- > > thanks, > > -- > Roberto Scattini >
Re: [GENERAL] streaming replication question
On Thursday, March 21, 2013, wd wrote: > You can combine warm standby and streaming, we do this in our product database. > When the standby is to far from the master, the slave will try to use restore_command to restore the database(warm standby), when the standby catch up the master, the steaming will working again. > > BTW: we use ominipitr. > ok. tomorrow i will check that again. i think that when restore_command was enabled in the slave, the wal receiver proccess didnt appear (and i didnt see the changes inmediately happening in the slave). can you post your recovery.conf? thanks for the reply! -- Roberto Scattini
[GENERAL] Problem in "Set search path"
-- Forwarded message -- From: Kalai R Date: Fri, Mar 22, 2013 at 9:36 AM Subject: Re: [GENERAL] Problem in "Set search path" To: Alban Hertroys Hi, Is that the same connection object in ,NET or the same connection to a connection pool or the same database connection? That's not necessarily the same. I use the same npgsql connection object in .net Might try temporarily turning up the logging in postgresql.conf to 'all' and see what is actually being done on the server. Did you know you can set a search_path on user (role) objects and on database objects in the database? That might just take out your need to specify it in your ,NET application. I try to find out my problem by your ideas. Thanks guys. Regards Kalai
