On Fri, Oct 1, 2010 at 9:13 AM, Michal Politowski <mpol+pg@meep.pl> wrote:
> EXPLAIN SELECT count(DISTINCT catalog.id) FROM catalog WHERE flag=false;
1: Try running explain analyze select ... here. It's far more informative.
2: select distinct is generally slower than using group by.
> QUERY PLAN
> -----------------------------------------------------------------------------------------
> Aggregate (cost=1615927.27..1615927.28 rows=1 width=8)
> -> Seq Scan on catalog (cost=0.00..1603214.56 rows=5085084 width=8)
> Filter: (NOT flag)
>
> SELECT pg_size_pretty(pg_relation_size('catalog'));
> pg_size_pretty
> ----------------
> 9380 MB
>
> Nothing else is going on the system, during the query disk reads rise from
> around 0 to > 100MB/s, so I would assume it should take a couple minutes
> and it takes ten times longer:
> Time: 1495549.716 ms
So that's about 9 Gigs read in 1495 seconds, or 6 Megs a second. Not real fast.
> What am I missing?
Hard to say. Have a look at these two pages:
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
And see if they help.
I'd run explain analyze and use iostat, vmstat, htop etc to see what
the machine is doing while the query is running.
--
To understand recursion, one must first understand recursion.