Re: Trivial query, large query, but very sad results. - Mailing list pgsql-general

> What's the query plan (see EXPLAIN)?  What fraction of the rows are
> actually selected by that WHERE clause?  Is there anything else
> going on in Postgres?

This is the only query running.

I browsed through the mail archive and came across the thread about poor performance
on large tables even when using indices, and someone suggested the use of the (for me previously unknown) cluster
command.

Using cluster, and then vacuum analyze, cut the count() query down from 12 minutes to
7 seconds. Amazing :)

Here's some info on the table and queries, if you're interested.

Table is now clustered on the service-index.
nnectionstats"
  Attribute  |           Type           | Modifier
-------------+--------------------------+----------
 time        | timestamp with time zone |
 client      | integer                  |
 server      | integer                  |
 protocol    | smallint                 |
 service     | character varying(80)    |
 server_port | integer                  |
 inbound     | bigint                   |
 outbound    | bigint                   |
Indices: service_idx,
         client_idx

 Index "service_idx"
 Attribute |         Type
-----------+-----------------------
 service   | character varying(80)
btree

 Index "client_idx"
 Attribute |  Type
-----------+---------
 client    | integer
btree

orvar=# explain verbose select count(*) from connectionstats where service='ftp'
;
NOTICE:  QUERY DUMP:

{ AGG :startup_cost 201487.12 :total_cost 201487.12 :rows 1 :width 0 :qptargetli
st ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname c
ount :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { AGGREG :a
ggname count :basetype 0 :aggtype 23 :target { CONST :consttype 23 :constlen 4 :
constbyval true :constisnull false :constvalue  4 [ 1 0 0 0 ] } :aggstar true :a
ggdistinct false }}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_
cost 201309.18 :rows 71177 :width 0 :qptargetlist <> :qpqual <> :lefttree <> :ri
ghttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 :indxid ( 11
162877) :indxqual (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 1062 :opid
 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1043 :vartyp
mod 84  :varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 1043 :constl
en -1 :constbyval false :constisnull false :constvalue  7 [ 7 0 0 0 102 116 112
] })})) :indxqualorig (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 1062 :
opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 5 :vartype 1043 :va
rtypmod 84  :varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 1043 :co
nstlen -1 :constbyval false :constisnull false :constvalue  7 [ 7 0 0 0 102 116
112 ] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm () :initplan <> :
nprm 0 }
NOTICE:  QUERY PLAN:

Aggregate  (cost=201487.12..201487.12 rows=1 width=0)
  ->  Index Scan using brunfitta on connectionstats  (cost=0.00..201309.18 rows=
71177 width=0)

EXPLAIN

orvar=# select timestamp 'now'; select count(*) from connectionstats WHERE client=1; select timestamp 'now';
        ?column?
------------------------
 2002-04-02 17:53:27+02
(1 row)

 count
-------
    65
(1 row)

        ?column?
------------------------
 2002-04-02 17:53:28+02
(1 row)

orvar=# select timestamp 'now'; select count(*) from connectionstats WHERE service='ftp'; select timestamp 'now';
        ?column?
------------------------
 2002-04-02 17:53:40+02
(1 row)

  count
---------
 1016614
(1 row)

        ?column?
------------------------
 2002-04-02 17:53:47+02
(1 row)

Now, in the real world, my queries are somewhat more complex than these, and I would like some tables to be "clustered"
onmore than one index.. which is confusing at best :) 
But this will do for now.. thanks for the input. (I'll go try 7.2 tomorrow and see if that helps even further).

Cheers,
Alexander