pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed<= Index.
15+ messages / 9 participants
[nested] [flat]
* <= Index.
@ 2002-03-31 04:08 Greg Sikorski <gte@atomicrevs.demon.co.uk>
2002-03-31 07:06 ` Re: <= Index. Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 15+ messages in thread
From: Greg Sikorski @ 2002-03-31 04:08 UTC (permalink / raw)
To: pgsql-admin
How come this Index isn't being used in the situation below? :) The field
is an epoch timestamp, and for various reasons it can't be a postgres
date/time type ;)
It does use the index with a direct = comparison.
Cheers, Greg
--
cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
suspend_expires <= 1017550117;
NOTICE: QUERY PLAN:
Seq Scan on levels (cost=0.00..13297.38 rows=395082 width=8) (actual
time=0.10..6647.88 rows=355869 loops=1)
Total runtime: 7492.36 msec
cmaster=# \d levels_suspendexpires_idx
Index "levels_suspendexpires_idx"
Column | Type
-----------------+---------
suspend_expires | integer
btree
--
cmaster=# select min(suspend_expires) from levels;
0
cmaster=# select max(suspend_expires) from levels;
1049662527
cmaster=# select avg(suspend_expires) from levels;
4555730.7985110746
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: <= Index.
2002-03-31 04:08 <= Index. Greg Sikorski <gte@atomicrevs.demon.co.uk>
@ 2002-03-31 07:06 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 15+ messages in thread
From: Peter Eisentraut @ 2002-03-31 07:06 UTC (permalink / raw)
To: Greg Sikorski <gte@atomicrevs.demon.co.uk>; +Cc: pgsql-admin
Greg Sikorski writes:
> How come this Index isn't being used in the situation below?
There's no point in using an index if you're retrieving nearly the entire
table.
--
Peter Eisentraut peter_e@gmx.net
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: <= Index.
@ 2002-03-31 14:48 Greg Sikorski <gte@atomicrevs.demon.co.uk>
2002-03-31 15:28 ` Re: <= Index. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Greg Sikorski @ 2002-03-31 14:48 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-admin
pgsql-admin-owner@postgresql.org wrote on 31/03/2002 08:06:52:
> Greg Sikorski writes:
>
> > How come this Index isn't being used in the situation below?
>
> There's no point in using an index if you're retrieving nearly the
entire
> table.
>
> --
> Peter Eisentraut peter_e@gmx.net
>
>
Well, that was probably a poor example to include, but it was nearly 4am
;)
Typically <20 results are returned, although it still says "Seq Scan", but
on a smaller subset.
--
cmaster=# SELECT user_id,channel_id FROM levels WHERE suspend_expires <=
1017589362 AND suspend_expires <> 0;
user_id | channel_id
---------+------------
...
(17 rows)
cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
suspend_expires <= 1017589362 AND suspend_expires <> 0;
NOTICE: QUERY PLAN:
Seq Scan on levels (cost=0.00..13709.09 rows=2609 width=8) (actual
time=208.98..1521.08 rows=17 loops=1)
Total runtime: 1521.29 msec
Cheers, Greg.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: <= Index.
2002-03-31 14:48 Re: <= Index. Greg Sikorski <gte@atomicrevs.demon.co.uk>
@ 2002-03-31 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2002-03-31 15:28 UTC (permalink / raw)
To: Greg Sikorski <gte@atomicrevs.demon.co.uk>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-admin
"Greg Sikorski" <gte@atomicrevs.demon.co.uk> writes:
> cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
> suspend_expires <= 1017589362 AND suspend_expires <> 0;
> NOTICE: QUERY PLAN:
> Seq Scan on levels (cost=0.00..13709.09 rows=2609 width=8) (actual
> time=208.98..1521.08 rows=17 loops=1)
> Total runtime: 1521.29 msec
First question is *can* the thing use an index? (Try "set enable_seqscan
to off" then explain again.) If not, it's probably a datatype
compatibility issue --- you'll need to quote or explicitly cast the
constant 1017589362 to match the type of suspend_expires.
If it could use an index but chooses not to, at least part of the
problem is the factor-of-100 overestimate in the number of matching
rows. That might be alleviated by increasing the statistics target
for the suspend_expires column. (ALTER TABLE SET STATISTICS, then
ANALYZE or VACUUM ANALYZE.)
You might also find that reducing random_page_cost produces better
indexscan cost estimates for your environment.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: <= Index.
@ 2002-03-31 19:25 Greg Sikorski <gte@atomicrevs.demon.co.uk>
0 siblings, 0 replies; 15+ messages in thread
From: Greg Sikorski @ 2002-03-31 19:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-admin
pgsql-admin-owner@postgresql.org wrote on 31/03/2002 16:28:09:
>
> First question is *can* the thing use an index? (Try "set
enable_seqscan
> to off" then explain again.) If not, it's probably a datatype
> compatibility issue --- you'll need to quote or explicitly cast the
> constant 1017589362 to match the type of suspend_expires.
>
Yep, it does use the index in that case:
---
cmaster=# set enable_seqscan to off;
SET VARIABLE
cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
suspend_expires <= 1017589362 AND suspend_expires <> 0;
NOTICE: QUERY PLAN:
Index Scan using levels_suspendexpires_idx on levels (cost=0.00..37098.40
rows=2787 width=8) (actual time=2551.05..2551.05 rows=0 loops=1)
Total runtime: 2551.17 msec
---
However its not much faster, so I took a look at the data distribution in
that table and quite a large amount of the data is 0 most of the time.
After a quick dig around some new 7.2 features I stumbled upon partial
index support:
---
cmaster=# \d levels_suspendexpires_idx
Index "levels_suspendexpires_idx"
Column | Type
-----------------+---------
suspend_expires | integer
btree
Index predicate: (suspend_expires <> 0)
---
cmaster=# explain analyze SELECT user_id,channel_id FROM levels WHERE
suspend_expires <= 1017605805 AND suspend_expires <> 0;
NOTICE: QUERY PLAN:
Index Scan using levels_suspendexpires_idx on levels (cost=0.00..267.65
rows=1621 width=8) (actual time=0.06..0.11 rows=6 loops=1)
Total runtime: 0.19 msec
---
Much better ;)
Thanks for your time and advice :)
Cheers, Greg.
^ permalink raw reply [nested|flat] 15+ messages in thread
* index
@ 2015-11-10 06:56 Artem Tomyuk <admin@leboutique.com>
2015-11-10 09:51 ` Re: index Alex Ignatov <a.ignatov@postgrespro.ru>
0 siblings, 1 reply; 15+ messages in thread
From: Artem Tomyuk @ 2015-11-10 06:56 UTC (permalink / raw)
To: pgsql-admin
Hello.
After upgrading to 9.4 from 9.1 i noticed that indexes recreating slower
from backup than it was on 9.1.
Is it possible?
I am making backup of db on 9.1 pg server and then restoring in on 9.4
every night.
Thanks
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: index
2015-11-10 06:56 index Artem Tomyuk <admin@leboutique.com>
@ 2015-11-10 09:51 ` Alex Ignatov <a.ignatov@postgrespro.ru>
0 siblings, 0 replies; 15+ messages in thread
From: Alex Ignatov @ 2015-11-10 09:51 UTC (permalink / raw)
To: Artem Tomyuk <admin@leboutique.com>; pgsql-admin
Hello!
What is your maintenance_work_mem parameter value in 9.4 and 9.1?
On 10.11.2015 9:56, Artem Tomyuk wrote:
> Hello.
>
> After upgrading to 9.4 from 9.1 i noticed that indexes recreating
> slower from backup than it was on 9.1.
> Is it possible?
>
> I am making backup of db on 9.1 pg server and then restoring in on 9.4
> every night.
> Thanks
--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres 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] 15+ messages in thread
* Index
@ 2024-03-10 11:19 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 11:48 ` Re: Index Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-10 11:50 ` Re: Index Holger Jakobs <holger@jakobs.com>
0 siblings, 2 replies; 15+ messages in thread
From: Rajesh Kumar @ 2024-03-10 11:19 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi ,
I have a table where size is increasing daily. In that table only inserts
and deletes happens. This table is referencing 15 other tables. We have
given delete cascade, so if a row is deleted in parent table, the matching
records will also be deleted in those child tables.
It is taking lot of time to delete. So, I am planning to create index on
those child tables.
My question is, apart from inserts, I am only going to delete records from
those tables. How index improves performance for deleting records? How
index is affected or how to do index management in this case?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Index
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-03-10 11:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 15+ messages in thread
From: Laurenz Albe @ 2024-03-10 11:48 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Sun, 2024-03-10 at 16:49 +0530, Rajesh Kumar wrote:
> I have a table where size is increasing daily. In that table only inserts and
> deletes happens. This table is referencing 15 other tables. We have given
> delete cascade, so if a row is deleted in parent table, the matching records
> will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index on those child tables.
>
> My question is, apart from inserts, I am only going to delete records from those
> tables. How index improves performance for deleting records? How index is affected
> or how to do index management in this case?
https://www.cybertec-postgresql.com/en/index-your-foreign-key/
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Index
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-03-10 11:50 ` Holger Jakobs <holger@jakobs.com>
2024-03-10 13:10 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Holger Jakobs @ 2024-03-10 11:50 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> Hi ,
>
> I have a table where size is increasing daily. In that table only
> inserts and deletes happens. This table is referencing 15 other
> tables. We have given delete cascade, so if a row is deleted in parent
> table, the matching records will also be deleted in those child tables.
>
> It is taking lot of time to delete. So, I am planning to create index
> on those child tables.
>
> My question is, apart from inserts, I am only going to delete records
> from those tables. How index improves performance for deleting
> records? How index is affected or how to do index management in this case?
Hi,
Since every single row which is going to be deleted in the child tables
has to be found, an index can be of great help here.
Actually, in most (not all!) cases an index on foreign key columns makes
sense.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../d6fb9d13-c682-cb87-804c-fd8a0ed50c84@jakobs.com/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Index
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 11:50 ` Re: Index Holger Jakobs <holger@jakobs.com>
@ 2024-03-10 13:10 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 18:09 ` Re: Index Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Rajesh Kumar @ 2024-03-10 13:10 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
What will happen if index is not managed in this case?
On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
> Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> > Hi ,
> >
> > I have a table where size is increasing daily. In that table only
> > inserts and deletes happens. This table is referencing 15 other
> > tables. We have given delete cascade, so if a row is deleted in parent
> > table, the matching records will also be deleted in those child tables.
> >
> > It is taking lot of time to delete. So, I am planning to create index
> > on those child tables.
> >
> > My question is, apart from inserts, I am only going to delete records
> > from those tables. How index improves performance for deleting
> > records? How index is affected or how to do index management in this
> case?
>
> Hi,
>
> Since every single row which is going to be deleted in the child tables
> has to be found, an index can be of great help here.
>
> Actually, in most (not all!) cases an index on foreign key columns makes
> sense.
>
> Regards,
>
> Holger
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Index
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 11:50 ` Re: Index Holger Jakobs <holger@jakobs.com>
2024-03-10 13:10 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-03-10 18:09 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-10 18:21 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Ron Johnson @ 2024-03-10 18:09 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
What does "if index is not managed" mean?
On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> What will happen if index is not managed in this case?
>
> On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
>
>> Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
>> > Hi ,
>> >
>> > I have a table where size is increasing daily. In that table only
>> > inserts and deletes happens. This table is referencing 15 other
>> > tables. We have given delete cascade, so if a row is deleted in parent
>> > table, the matching records will also be deleted in those child tables.
>> >
>> > It is taking lot of time to delete. So, I am planning to create index
>> > on those child tables.
>> >
>> > My question is, apart from inserts, I am only going to delete records
>> > from those tables. How index improves performance for deleting
>> > records? How index is affected or how to do index management in this
>> case?
>>
>> Hi,
>>
>> Since every single row which is going to be deleted in the child tables
>> has to be found, an index can be of great help here.
>>
>> Actually, in most (not all!) cases an index on foreign key columns makes
>> sense.
>>
>> Regards,
>>
>> Holger
>>
>> --
>> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>>
>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Index
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 11:50 ` Re: Index Holger Jakobs <holger@jakobs.com>
2024-03-10 13:10 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 18:09 ` Re: Index Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-03-10 18:21 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 18:29 ` Re: Index Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-10 18:32 ` Re: Index Holger Jakobs <holger@jakobs.com>
0 siblings, 2 replies; 15+ messages in thread
From: Rajesh Kumar @ 2024-03-10 18:21 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
If we are deleting rows often.....I am talking ABT the possibility of index
bloat.
On Sun, 10 Mar 2024, 23:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
> What does "if index is not managed" mean?
>
> On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
> wrote:
>
>> What will happen if index is not managed in this case?
>>
>> On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
>>
>>> Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
>>> > Hi ,
>>> >
>>> > I have a table where size is increasing daily. In that table only
>>> > inserts and deletes happens. This table is referencing 15 other
>>> > tables. We have given delete cascade, so if a row is deleted in parent
>>> > table, the matching records will also be deleted in those child
>>> tables.
>>> >
>>> > It is taking lot of time to delete. So, I am planning to create index
>>> > on those child tables.
>>> >
>>> > My question is, apart from inserts, I am only going to delete records
>>> > from those tables. How index improves performance for deleting
>>> > records? How index is affected or how to do index management in this
>>> case?
>>>
>>> Hi,
>>>
>>> Since every single row which is going to be deleted in the child tables
>>> has to be found, an index can be of great help here.
>>>
>>> Actually, in most (not all!) cases an index on foreign key columns makes
>>> sense.
>>>
>>> Regards,
>>>
>>> Holger
>>>
>>> --
>>> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>>>
>>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Index
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 11:50 ` Re: Index Holger Jakobs <holger@jakobs.com>
2024-03-10 13:10 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 18:09 ` Re: Index Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-10 18:21 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-03-10 18:29 ` Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Ron Johnson @ 2024-03-10 18:29 UTC (permalink / raw)
To: Rajesh Kumar <rajeshkumar.dba09@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Tune your autovacuum settings.
On Sun, Mar 10, 2024 at 2:21 PM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
wrote:
> If we are deleting rows often.....I am talking ABT the possibility of
> index bloat.
>
> On Sun, 10 Mar 2024, 23:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
>> What does "if index is not managed" mean?
>>
>> On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar <rajeshkumar.dba09@gmail.com>
>> wrote:
>>
>>> What will happen if index is not managed in this case?
>>>
>>> On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com> wrote:
>>>
>>>> Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
>>>> > Hi ,
>>>> >
>>>> > I have a table where size is increasing daily. In that table only
>>>> > inserts and deletes happens. This table is referencing 15 other
>>>> > tables. We have given delete cascade, so if a row is deleted in
>>>> parent
>>>> > table, the matching records will also be deleted in those child
>>>> tables.
>>>> >
>>>> > It is taking lot of time to delete. So, I am planning to create index
>>>> > on those child tables.
>>>> >
>>>> > My question is, apart from inserts, I am only going to delete records
>>>> > from those tables. How index improves performance for deleting
>>>> > records? How index is affected or how to do index management in this
>>>> case?
>>>>
>>>> Hi,
>>>>
>>>> Since every single row which is going to be deleted in the child tables
>>>> has to be found, an index can be of great help here.
>>>>
>>>> Actually, in most (not all!) cases an index on foreign key columns
>>>> makes
>>>> sense.
>>>>
>>>> Regards,
>>>>
>>>> Holger
>>>>
>>>> --
>>>> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>>>>
>>>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Index
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 11:50 ` Re: Index Holger Jakobs <holger@jakobs.com>
2024-03-10 13:10 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 18:09 ` Re: Index Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-10 18:21 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
@ 2024-03-10 18:32 ` Holger Jakobs <holger@jakobs.com>
1 sibling, 0 replies; 15+ messages in thread
From: Holger Jakobs @ 2024-03-10 18:32 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 10.03.24 um 19:21 schrieb Rajesh Kumar:
> If we are deleting rows often.....I am talking ABT the possibility of
> index bloat.
>
> On Sun, 10 Mar 2024, 23:40 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
>
> What does "if index is not managed" mean?
>
> On Sun, Mar 10, 2024 at 9:11 AM Rajesh Kumar
> <rajeshkumar.dba09@gmail.com> wrote:
>
> What will happen if index is not managed in this case?
>
> On Sun, 10 Mar 2024, 17:20 Holger Jakobs, <holger@jakobs.com>
> wrote:
>
> Am 10.03.24 um 12:19 schrieb Rajesh Kumar:
> > Hi ,
> >
> > I have a table where size is increasing daily. In that
> table only
> > inserts and deletes happens. This table is referencing
> 15 other
> > tables. We have given delete cascade, so if a row is
> deleted in parent
> > table, the matching records will also be deleted in
> those child tables.
> >
> > It is taking lot of time to delete. So, I am planning to
> create index
> > on those child tables.
> >
> > My question is, apart from inserts, I am only going to
> delete records
> > from those tables. How index improves performance for
> deleting
> > records? How index is affected or how to do index
> management in this case?
>
> Hi,
>
> Since every single row which is going to be deleted in the
> child tables
> has to be found, an index can be of great help here.
>
> Actually, in most (not all!) cases an index on foreign key
> columns makes
> sense.
>
> Regards,
>
> Holger
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>
Are your deleting arbitrary rows - or are the data timeseries, where you
delete all data of weeks or months?
If so, time-partition your tables and drop partitions when necessary.
If not, tune autovacuum or vacuum full your tables and indexes every now
and then. Or re-index concurrently sometimes.
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
chat with me on Signal messenger
<https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg;
Attachments:
[image/png] yN6KW349IqGU3o59.png (551B, ../../d227ce10-a52f-6747-9138-9ed01f83ef63@jakobs.com/3-yN6KW349IqGU3o59.png)
download | view image
[application/pgp-signature] OpenPGP_signature (202B, ../../d227ce10-a52f-6747-9138-9ed01f83ef63@jakobs.com/4-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2024-03-10 18:32 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-31 04:08 <= Index. Greg Sikorski <gte@atomicrevs.demon.co.uk>
2002-03-31 07:06 ` Peter Eisentraut <peter_e@gmx.net>
2002-03-31 14:48 Re: <= Index. Greg Sikorski <gte@atomicrevs.demon.co.uk>
2002-03-31 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-31 19:25 Re: <= Index. Greg Sikorski <gte@atomicrevs.demon.co.uk>
2015-11-10 06:56 index Artem Tomyuk <admin@leboutique.com>
2015-11-10 09:51 ` Re: index Alex Ignatov <a.ignatov@postgrespro.ru>
2024-03-10 11:19 Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 11:48 ` Re: Index Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-10 11:50 ` Re: Index Holger Jakobs <holger@jakobs.com>
2024-03-10 13:10 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 18:09 ` Re: Index Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-10 18:21 ` Re: Index Rajesh Kumar <rajeshkumar.dba09@gmail.com>
2024-03-10 18:29 ` Re: Index Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-10 18:32 ` Re: Index Holger Jakobs <holger@jakobs.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