| From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
|---|---|
| To: | Dimitrios Apostolou <jimis(at)gmx(dot)net> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches |
| Date: | 2023-02-01 18:26:12 |
| Message-ID: | CAKFQuwbL81QYqyCofcw6uw5=0Jictdiw8U2=SRHh5EFFT9W13w@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
On Wed, Feb 1, 2023 at 11:15 AM Dimitrios Apostolou <jimis(at)gmx(dot)net> wrote:
> On Tue, 31 Jan 2023, David G. Johnston wrote:
> >
> > It feels like there is room for improvement here using table statistics
> and the visibility map to significantly reduce the number of pages
> retrieved that turn out to be all dead.
>
> Yes, I too wish postgres was more clever for a simple unordered query. I
> should also mention that this table has two btree indices, one primary key
> index and one on another column. Would it be feasible to "descend" an
> index in order to immediately find some live data?
>
That isn't possible since indexes don't have tuple visibility information
within them; most of the dead bloat entries present in the table have dead
bloat entries pointing to them in the index.
David J.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dimitrios Apostolou | 2023-02-01 18:27:34 | VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches) |
| Previous Message | Dimitrios Apostolou | 2023-02-01 18:15:19 | Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches |