Re: [PERFORM] Slow counting on v9.3
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_Countinghttp://wiki.postgresql.org/wiki/Slow_Counting On Mon, Jan 13, 2014 at 11:57 PM, Kai Sellgren kaisellg...@gmail.comwrote: 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
Re: [PERFORM] Slow counting on v9.3
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
Re: [PERFORM] Issue with query scanning through all data even with indexes
From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Kai Sellgren Sent: Thursday, January 09, 2014 4:37 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Issue with query scanning through all data even with indexes Hi, I have a query that has each field used in conditions + sort indexed, but it scans through all data. The query in question looks like: http://pastie.org/8618562 I have each of those condition fields indexed: NewsArticle.groupId NewsArticle.sharedToCommunityIds NewsArticle.sourceFilterIds CommunityGroupLink.communityId CommunityGroupLink.groupId SourceFilter.groupId SourceFilter.communityId This is the data output for explain http://d.pr/i/VGT3 And in visual http://d.pr/i/mqiN Line 7 says rows=99173 which makes it real slow (it can take up to a minute to run). Do you have any ideas? All of them are appreciated! Cheers, -- Yours sincerely, Kai Sellgren Could you try to move WHERE clause conditions into JOIN conditions, something like this: SELECT NewsArticle.id FROM NewsArticle LEFT JOIN CommunityGroupLink ON CommunityGroupLink.communityId = 1538 AND (CommunityGroupLink.groupId = NewsArticle.groupId) AND((1538 = ANY (NewsArticle.sharedToCommunityIds) OR (CommunityGroupLink.id IS NOT NULL))) LEFT JOIN SourceFilter ON SourceFilter.communityId = 1538 AND SourceFilter.groupId = NewsArticle.groupId AND((SourceFilter.id IS NULL OR SourceFilter.id = ANY(NewsArticle.sourceFilterIds))); Not sure what you do with LIMIT 35 - it's not shown in explain plan. Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
