pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow counting on v9.3
3+ messages / 3 participants
[nested] [flat]
* Slow counting on v9.3
@ 2014-01-13 21:57 Kai Sellgren <kaisellgren@gmail.com>
2014-01-16 08:37 ` Re: Slow counting on v9.3 Mehmet Çakoğlu <mehmetcakoglu@gmail.com>
2014-01-16 08:42 ` Re: Slow counting on v9.3 Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 2 replies; 3+ messages in thread
From: Kai Sellgren @ 2014-01-13 21:57 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm experiecing slow count performance:
SELECT COUNT(*) AS "count"
FROM "NewsArticle"
Takes 210 ms. I've run analyze and vacuum. I'm on 9.3. Here're the stats
http://d.pr/i/6YoB
I don't understand why is it that slow. It returns 1 integer, and counts
without filters.
This performs quickly:
SELECT reltuples AS count
FROM pg_class
WHERE relname = 'NewsArticle';
But I'd like to add conditions so I don't like the last method.
--
Yours sincerely,
Kai Sellgren
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow counting on v9.3
2014-01-13 21:57 Slow counting on v9.3 Kai Sellgren <kaisellgren@gmail.com>
@ 2014-01-16 08:37 ` Mehmet Çakoğlu <mehmetcakoglu@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Mehmet Çakoğlu @ 2014-01-16 08:37 UTC (permalink / raw)
To: Kai Sellgren <kaisellgren@gmail.com>; +Cc: pgsql-performance
Hi Kai,
You are right, postgresql Count() function is slow, because; It's
physically count the rows one by one.
Other database systems using indexes for counting, but postgresql walk
through all rows in multiple transactions with different row states for
calculating the real row count. This is about architecture of postgresql.
If you use WHERE condition on indexed column in your query, this will be
much faster.
Source: http://wiki.postgresql.org/wiki/Slow_Counting<http://wiki.postgresql.org/wiki/Slow_Counting;
On Mon, Jan 13, 2014 at 11:57 PM, Kai Sellgren <kaisellgren@gmail.com>wrote:
> Hi,
>
> I'm experiecing slow count performance:
>
> SELECT COUNT(*) AS "count"
> FROM "NewsArticle"
>
> Takes 210 ms. I've run analyze and vacuum. I'm on 9.3. Here're the stats
> http://d.pr/i/6YoB
>
> I don't understand why is it that slow. It returns 1 integer, and counts
> without filters.
>
> This performs quickly:
>
> SELECT reltuples AS count
> FROM pg_class
> WHERE relname = 'NewsArticle';
>
> But I'd like to add conditions so I don't like the last method.
>
>
> --
> Yours sincerely,
> Kai Sellgren
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Slow counting on v9.3
2014-01-13 21:57 Slow counting on v9.3 Kai Sellgren <kaisellgren@gmail.com>
@ 2014-01-16 08:42 ` Guillaume Cottenceau <gc@mnc.ch>
1 sibling, 0 replies; 3+ messages in thread
From: Guillaume Cottenceau @ 2014-01-16 08:42 UTC (permalink / raw)
To: Kai Sellgren <kaisellgren@gmail.com>; +Cc: pgsql-performance
Kai Sellgren <kaisellgren 'at' gmail.com> writes:
> Hi,
>
> I'm experiecing slow count performance:
>
> SELECT COUNT(*) AS "count"
> FROM "NewsArticle"
>
> Takes 210 ms. I've run analyze and vacuum. I'm on 9.3. Here're the stats http:/
> /d.pr/i/6YoB
>
> I don't understand why is it that slow. It returns 1 integer, and counts
> without filters.
You might actually have a lot more dead tuples than reported in
statistic. Last vacuum is old according to your screenshot. Try
"VACUUM FULL ANALYZE" on your table, then try again counting.
> This performs quickly:
>
> SELECT reltuples AS count
> FROM pg_class
> WHERE relname = 'NewsArticle';
This is not the same. This one uses precomputed statistics, and
doesn't scan the actual table data.
> But I'd like to add conditions so I don't like the last method.
>
>
> --
> Yours sincerely,
> Kai Sellgren
--
Guillaume Cottenceau
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-01-16 08:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-01-13 21:57 Slow counting on v9.3 Kai Sellgren <kaisellgren@gmail.com>
2014-01-16 08:37 ` Mehmet Çakoğlu <mehmetcakoglu@gmail.com>
2014-01-16 08:42 ` Guillaume Cottenceau <gc@mnc.ch>
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