Re: Why could different data in a table be processed with different performance?
| От | Vladimir Ryabtsev |
|---|---|
| Тема | Re: Why could different data in a table be processed with different performance? |
| Дата | |
| Msg-id | CAMqTPq=f2fQ8tnUu3UxrvfogiDCn+04HWFX8fpZkDoeYQ7ktTQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Why could different data in a table be processed with differentperformance? (Justin Pryzby <pryzby@telsasoft.com>) |
| Ответы |
Re: Why could different data in a table be processed with differentperformance?
Re: Why could different data in a table be processed with differentperformance? |
| Список | pgsql-performance |
> This seems significant..it means the heap was probably written in backwards
order relative to the IDs, and the OS readahead is ineffective when index
scanning across a range of IDs.
But again, why is it different for one range and another? It was reversed for both ranges.
> I would definitely want to make that a partitioned table
Yes, I believe it will be partitioned in the future.
> I *suspect* VACUUM FULL won't help, since (AIUI) it copies all "visible" tuples from the source table into a new table (and updates indices as necessary). It can resolve bloat due to historic DELETEs, but since I think your table was written in reverse order of pkey, I think it'll also copy it in reverse order.
I am going copy the slow range into a table nearby and see if it reproduces (I hope "INSERT INTO t2 SELECT * FROM t1 WHERE ..." will keep existing order of rows). Then I could try the same after CLUSTER.
Regards,
Vlad
order relative to the IDs, and the OS readahead is ineffective when index
scanning across a range of IDs.
But again, why is it different for one range and another? It was reversed for both ranges.
> I would definitely want to make that a partitioned table
Yes, I believe it will be partitioned in the future.
> I *suspect* VACUUM FULL won't help, since (AIUI) it copies all "visible" tuples from the source table into a new table (and updates indices as necessary). It can resolve bloat due to historic DELETEs, but since I think your table was written in reverse order of pkey, I think it'll also copy it in reverse order.
I am going copy the slow range into a table nearby and see if it reproduces (I hope "INSERT INTO t2 SELECT * FROM t1 WHERE ..." will keep existing order of rows). Then I could try the same after CLUSTER.
Regards,
Vlad
В списке pgsql-performance по дате отправления: