Re: The Last Optimization

Have you run an analyze on your database?  It makes a big difference.

If that doesn't work, do a quick test, set the seq scan to off like so:

set enable_seqscan=off;

and rerun your query.  If that fixes the problem, but it comes back with
seqscan=off, then you might need to edit your $PGDATA/postgresql.conf file
and change a couple of things.  I have been setting random page cost to
1.5 or so lately, as my machine has pretty fast drives for seek times (4
disk raid array) but slow for massive reads (10 Megs a second on each
drive)  Also, lowering cpu_index_tuple_cost will drive the planner towards
using index scans.

the problem in general is that if the planner accidentally picking an
index scan can make a slow query a little slower, but accidentally picking
a sequential scan can make a sub second query into a multi-minute
nightmare wait.

On Fri, 6 Sep 2002, Areski Belaid wrote:

> First Thanks for all of your advice, It's really nice to get so much help...
>
> I follow some advice and after try to do some EXPLAIN ANALYSE on every
> queries, I realyse that
> a "SELECT with LIMITE" (ie 50 100) is really fast quiet immediate...
> BUT That's create the problem in my application is the SELECT COUNT.
>
> Ok, I did some "select count" on few hundred thousand of instance (million
> some time)...
> The "select count" have to check all of them and it's not the case with
> "LIMIT"! Right ?
>
>
> EXPLAIN ANALYZE SELECT count(*) FROM "Data" WHERE ("IDOrigin" IN ('16',
> '20', '21', '18', '13', '17', '15', '19'));
>
>
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=188017.51..188017.51 rows=1 width=0) (actual
> time=72071.90..72071.90 rows=1 loops=1)
>   ->  Seq Scan on Email  (cost=0.00..185740.10 rows=910965 width=0) (actual
> time=15988.85..71825.27 rows=183065 loops=1)
> Total runtime: 72072.12 msec
>
>
> 72 secondes for a php/pg application is useless.
>
>
> So which is the way, I need the "select count" to kwon the globaly number, I
> can avoid  of this information...
> A cache solution, would be impossible, my search engine is really complex...
> So maybe split the table in different other table, but it's going to take
> one week of work if I have to change
> all the queries...
>
>
> So, I m a less lost but always without solution, every help would nice...
>
> Best regards, Areski
>