pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReg. Restore
6+ messages / 3 participants
[nested] [flat]
* Reg. Restore
@ 2013-10-07 12:03 Venakata Ramana <ramana.pls@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Venakata Ramana @ 2013-10-07 12:03 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am using postgresql 9.1.5. on windows Xp.
I am facing two problems:
1. Restore of DB is very slow.
How to improve the speed of Restore?
2. Without indexes, retrieving from table is fast?
After creating an index on that table, then the retrieval become slow.
plz prove any solution
Thanks in advance
Regards
Ramana
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Reg. Restore
@ 2013-10-07 17:13 Kevin Grittner <kgrittn@ymail.com>
parent: Venakata Ramana <ramana.pls@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2013-10-07 17:13 UTC (permalink / raw)
To: Venakata Ramana <ramana.pls@gmail.com>; pgsql-admin
Venakata Ramana <ramana.pls@gmail.com> wrote:
> I am using postgresql 9.1.5. on windows Xp.
You should update to a more current minor release.
http://www.postgresql.org/support/versioning/
> 1. Restore of DB is very slow.
> How to improve the speed of Restore?
You need to provide more information on how you backed up, how you
are restoring, and what the bottleneck seems to be.
http://www.postgresql.org/docs/9.1/interactive/backup.html
> 2. Without indexes, retrieving from table is fast?
> After creating an index on that table, then the retrieval
> become slow.
There's not enough information to give much advice. Please read
this page and start a new thread on the pgsql-performance list:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Reg. Restore
@ 2013-10-07 17:34 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: Venakata Ramana <ramana.pls@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Matheus de Oliveira @ 2013-10-07 17:34 UTC (permalink / raw)
To: Venakata Ramana <ramana.pls@gmail.com>; +Cc: pgsql-admin
On Mon, Oct 7, 2013 at 9:03 AM, Venakata Ramana <ramana.pls@gmail.com>wrote:
> Hi,
>
> I am using postgresql 9.1.5. on windows Xp.
>
>
You should update to 9.1.9.
I am facing two problems:
>
> 1. Restore of DB is very slow.
> How to improve the speed of Restore?
>
>
How are you doing this restore? Is it from a dump? Are you using or could
you use custom format?
At [1] there are some tips to help.
2. Without indexes, retrieving from table is fast?
> After creating an index on that table, then the retrieval become slow.
>
>
That is odd. Perhaps your indexes are bloat or you don't have proper
statics on your tables?! Please, give us the EXPLAIN ANALYZE of the queries
with and without the indexes (you could use [2]), and run ANALYZE on the
table before it.
[1] http://www.postgresql.org/docs/9.1/static/populate.html
[2] http://explain.depesz.com/
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Reg. Restore
@ 2013-10-08 05:43 Venakata Ramana <ramana.pls@gmail.com>
parent: Matheus de Oliveira <matioli.matheus@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Venakata Ramana @ 2013-10-08 05:43 UTC (permalink / raw)
To: Matheus de Oliveira <matioli.matheus@gmail.com>; +Cc: pgsql-admin
Thanks for your reply.
---> You should update to 9.1.9.
Recently I updated to 9.1.5. I will do in near future.
--> How are you doing this restore? Is it from a dump? Are you using or
could you use custom format?
I am pg_restore using for restoring the backup which created using pg_dump.
--> That is odd. Perhaps your indexes are bloat or you don't have proper
statics on your tables?! Please, give us the EXPLAIN ANALYZE of the queries
with and without the indexes (you could use [2]), and run ANALYZE on the
table before it.
I used [2] option.
With Index, and With out Index came in White Color.
In this nDl and Nqr are query Plans i pasted. Please go through that.
Suggest me whether i am doing in correct way or not.
Thanks in Advance.
Regards
Ramana
On Mon, Oct 7, 2013 at 11:04 PM, Matheus de Oliveira <
matioli.matheus@gmail.com> wrote:
>
>
>
> On Mon, Oct 7, 2013 at 9:03 AM, Venakata Ramana <ramana.pls@gmail.com>wrote:
>
>> Hi,
>>
>> I am using postgresql 9.1.5. on windows Xp.
>>
>>
> You should update to 9.1.9.
>
>
> I am facing two problems:
>>
>> 1. Restore of DB is very slow.
>> How to improve the speed of Restore?
>>
>>
> How are you doing this restore? Is it from a dump? Are you using or could
> you use custom format?
>
> At [1] there are some tips to help.
>
>
> 2. Without indexes, retrieving from table is fast?
>> After creating an index on that table, then the retrieval become slow.
>>
>>
> That is odd. Perhaps your indexes are bloat or you don't have proper
> statics on your tables?! Please, give us the EXPLAIN ANALYZE of the queries
> with and without the indexes (you could use [2]), and run ANALYZE on the
> table before it.
>
>
> [1] http://www.postgresql.org/docs/9.1/static/populate.html
> [2] http://explain.depesz.com/
>
> Regards,
> --
> Matheus de Oliveira
> Analista de Banco de Dados
> Dextra Sistemas - MPS.Br nível F!
> www.dextra.com.br/postgres
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Reg. Restore
@ 2013-10-08 06:52 Venakata Ramana <ramana.pls@gmail.com>
parent: Kevin Grittner <kgrittn@ymail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Venakata Ramana @ 2013-10-08 06:52 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; +Cc: pgsql-admin
Thanks for your reply.
---> You should update to a more current minor release.
Recently I updated to 9.1.5. I will do in near future.
--> You need to provide more information on how you backed up, how you
are restoring, and what the bottleneck seems to be.
I am pg_restore using for restoring the backup which created using pg_dump.
Thanks in Advance
Regards
Ramana
On Mon, Oct 7, 2013 at 10:43 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
> Venakata Ramana <ramana.pls@gmail.com> wrote:
>
> > I am using postgresql 9.1.5. on windows Xp.
>
> You should update to a more current minor release.
>
> http://www.postgresql.org/support/versioning/
>
> > 1. Restore of DB is very slow.
> > How to improve the speed of Restore?
>
> You need to provide more information on how you backed up, how you
> are restoring, and what the bottleneck seems to be.
>
> http://www.postgresql.org/docs/9.1/interactive/backup.html
>
>
> > 2. Without indexes, retrieving from table is fast?
> > After creating an index on that table, then the retrieval
> > become slow.
>
> There's not enough information to give much advice. Please read
> this page and start a new thread on the pgsql-performance list:
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Reg. Restore
@ 2013-10-08 12:19 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: Venakata Ramana <ramana.pls@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Matheus de Oliveira @ 2013-10-08 12:19 UTC (permalink / raw)
To: Venakata Ramana <ramana.pls@gmail.com>; +Cc: pgsql-admin
On Tue, Oct 8, 2013 at 2:43 AM, Venakata Ramana <ramana.pls@gmail.com>wrote:
> Thanks for your reply.
>
>
> ---> You should update to 9.1.9.
>
> Recently I updated to 9.1.5. I will do in near future.
>
>
Recently??? 9.1.5 was released more then one year ago... =/
>
> --> How are you doing this restore? Is it from a dump? Are you using or
> could you use custom format?
>
>
> I am pg_restore using for restoring the backup which created using
> pg_dump.
>
>
>
If you don't show how you are doing that, nobody will be able to help you.
>
> --> That is odd. Perhaps your indexes are bloat or you don't have proper
> statics on your tables?! Please, give us the EXPLAIN ANALYZE of the queries
> with and without the indexes (you could use [2]), and run ANALYZE on the
> table before it.
>
>
> I used [2] option.
>
> With Index, and With out Index came in White Color.
>
> In this nDl and Nqr are query Plans i pasted. Please go through that.
> Suggest me whether i am doing in correct way or not.
>
>
I'm not going there to search for your plans, you could help people to help
you and pasted it here by yourself. Also, as Kevin suggested, this subject
should be on another thread in performance list.
>
>
>
>
> On Mon, Oct 7, 2013 at 11:04 PM, Matheus de Oliveira <
> matioli.matheus@gmail.com> wrote:
>
>>
>>
>>
>> On Mon, Oct 7, 2013 at 9:03 AM, Venakata Ramana <ramana.pls@gmail.com>wrote:
>>
>>> Hi,
>>>
>>> I am using postgresql 9.1.5. on windows Xp.
>>>
>>>
>> You should update to 9.1.9.
>>
>>
>> I am facing two problems:
>>>
>>> 1. Restore of DB is very slow.
>>> How to improve the speed of Restore?
>>>
>>>
>> How are you doing this restore? Is it from a dump? Are you using or could
>> you use custom format?
>>
>> At [1] there are some tips to help.
>>
>>
>> 2. Without indexes, retrieving from table is fast?
>>> After creating an index on that table, then the retrieval become
>>> slow.
>>>
>>>
>> That is odd. Perhaps your indexes are bloat or you don't have proper
>> statics on your tables?! Please, give us the EXPLAIN ANALYZE of the queries
>> with and without the indexes (you could use [2]), and run ANALYZE on the
>> table before it.
>>
>>
>> [1] http://www.postgresql.org/docs/9.1/static/populate.html
>> [2] http://explain.depesz.com/
>>
>> Regards,
>> --
>> Matheus de Oliveira
>> Analista de Banco de Dados
>> Dextra Sistemas - MPS.Br nível F!
>> www.dextra.com.br/postgres
>>
>>
>
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-10-08 12:19 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-07 12:03 Reg. Restore Venakata Ramana <ramana.pls@gmail.com>
2013-10-07 17:13 ` Kevin Grittner <kgrittn@ymail.com>
2013-10-08 06:52 ` Venakata Ramana <ramana.pls@gmail.com>
2013-10-07 17:34 ` Matheus de Oliveira <matioli.matheus@gmail.com>
2013-10-08 05:43 ` Venakata Ramana <ramana.pls@gmail.com>
2013-10-08 12:19 ` Matheus de Oliveira <matioli.matheus@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