From: | Grzegorz Kuczera <grzegorz(dot)kuczera(at)comarch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | select and count efficiency (~35 mln rows) |
Date: | 2016-01-05 13:07:22 |
Message-ID: | 568BC00A.1050204@comarch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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).
I use the c3p0 library to manage the pool connections to the database
and what I get is the timeout: both queries (separately) can take longer
than 150 seconds. If I execute the query straight on the database
(without usage of jdbc), it sometimes takes even 11 minutes to complete.
Month or two ago I vacuumed the database manually. Now I can see - in
the last_vacuum and last_autovacuum from the pg_stat_all_tables - that
the nulls are written there.
There are about 35 milion records in the table.
Details:
* version - PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by
gcc (Debian 4.7.2-5) 4.7.2, 64-bit
* result of select pg_postmaster_start_time() - 2015-12-22
12:03:55.471436+01
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
I would appreciate any help/tips.
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2016-01-05 13:16:42 | Re: select and count efficiency (~35 mln rows) |
Previous Message | Andreas Kretschmer | 2016-01-05 12:24:54 | Re: Streaming replication stacked. |