From: Mark Kirkwood <markir@i4free.co.nz>
To: dave@slickness.org
To: feblec@ig.com.br
To: jks@selectacast.netT
Cc: pgsql-sql@postgresql.org
Subject: Re: Select very slow
Date: Tue, 20 Mar 2001 20:59:58 +1200
Message-ID: <01032020595800.00632@spikey.slithery.org> (raw)
In-Reply-To: <200103200127.f2K1RZN79198@mail.postgresql.org>
References: <200103200127.f2K1RZN79198@mail.postgresql.org>
>
> That 'count(*)' is going to be slow.
> Try counting a column that's indexed (p.doc might work?)
That is not the case, you can convince yourself with explain - you get the
same plan(s) :
e.g : ( table dim0 with indexed column d0key ) ...
explain select count(*) from dim0 where d0key < 1000;
Aggregate (cost=96.13..96.13 rows=1 width=0)
-> Index Scan using dim0_pk on dim0 (cost=0.00..93.63 rows=1000 width=0)
explain select count(d0key) from dim0 where d0key < 1000;
Aggregate (cost=96.13..96.13 rows=1 width=4)
-> Index Scan using dim0_pk on dim0 (cost=0.00..93.63 rows=1000 width=4)
> (the tables and query snipped..).
> anyone help-me ?
>
I would try to make the primary key ( doc) a fixed length varchar(n) instead
of text if possible, as text is intended for very long strings and btree
indexes usually perform best on (small) fixed length columns.
If your key is going to be a really long string then maybe rethink the design
( use a "synthetic" key like a sequence or integer checksum of doc to index
on ).
perform a vacuum analyze and then post the output of the explain below to
this list !
explain select p.city,count(*) from sales s, person p where s.doc = p.doc
group by p.city;
Cheers
Mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: markir@i4free.co.nz, dave@slickness.org, feblec@ig.com.br, jks@selectacast.netT
Subject: Re: Select very slow
In-Reply-To: <01032020595800.00632@spikey.slithery.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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