Re: select and count efficiency (~35 mln rows)

Grzegorz Kuczera <grzegorz(dot)kuczera(at)comarch(dot)com> wrote:

> This is my first question here, so I would like to say hello to everyone:)
>
> In my case, the problem appears when I want to fetch some data to inflate the
> table with it. First of all, I am counting the records from the table (for
> paging, over indexed column), then the select query is performed (with limit
> equal to 15).

tl;dr.

Please read the links below:

http://www.depesz.com/2007/08/29/better-results-paging-in-postgresql-82/
https://wiki.postgresql.org/wiki/File:Pagination_Done_the_PostgreSQL_Way.pdf
https://coderwall.com/p/lkcaag

>
> There are about 35 milion records in the table.

do you really want to show all records, even with pagination? REALLY?

>
> And here is the question, which I posted on the Stackoverflow two months ago:
> http://stackoverflow.com/questions/33009865/
> why-index-only-scan-is-taking-so-long

Because of MVCC / visibility a seq-scan is requisite.

For a guestimate about the number of rows you can look into pg_class.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°