Re: Performance problem on RH7.1 - Mailing list pgsql-general

Hi Tom,

> Good, but you're not there yet --- the Sort step shouldn't be there at
> all.  You've still got some inconsistency between the ORDER BY and the
> index.  Check my example again.
yes yes I missed that, sorry. Now don't mention the performance because I
couldn' see anything but the result. :)

In general I'd like to draw the consequences. What kind of theories should I
keep in mind when I want to choose an appropriate index key? I ask it
bacause I'm trying to optimize an other query of mine and I'm facing some
more problems.

I have this query: (note, that the planner uses t_stockchanges_fullindex,
instead of t_stockchanges_fullindex4 which is exactly what I would need)
========================
explain analyze select getup, (select dir from t_changes where id=changeid)
as dir
from t_stockchanges where stockid='1' and productid='428' and
date>='2004.06.01' and date<='2004.06.29'
order by stockid, productid, date;
QUERY PLAN
Sort  (cost=7.17..7.17 rows=1 width=46) (actual time=3.00..3.00 rows=5
loops=1)
  Sort Key: stockid, productid, date
  ->  Index Scan using t_stockchanges_fullindex on t_stockchanges
(cost=0.00..7.16 rows=1 width=46) (actual time=1.00..3.00 rows=5 loops=1)
        Index Cond: ((date >= '2004.06.01'::bpchar) AND (date <=
'2004.06.29'::bpchar) AND (stockid = 1) AND (productid = 428))
        SubPlan
          ->  Seq Scan on t_changes  (cost=0.00..1.16 rows=1 width=5)
(actual time=0.00..0.00 rows=1 loops=5)
                Filter: (id = $0)
Total runtime: 3.00 msec
========================

And these indexes:

========================
CREATE INDEX t_stockchanges_fullindex4
  ON t_stockchanges
  USING btree
  (stockid, productid, date);

DROP INDEX t_stockchanges_fullindex3;
CREATE INDEX t_stockchanges_fullindex3
  ON t_stockchanges
  USING btree
  (stockid, productid, changeid, date, time);

DROP INDEX t_stockchanges_fullindex;
CREATE INDEX t_stockchanges_fullindex
  ON t_stockchanges
  USING btree
  (date, stockid, productid, changeid);
========================

If I delete the index t_stockchanges_fullindex, I get the following (better)
result.

========================
DROP INDEX t_stockchanges_fullindex;

explain analyze select getup, (select dir from t_changes where id=changeid)
as dir
from t_stockchanges where stockid='1' and productid='428' and
date>='2004.06.01' and date<='2004.06.29'
order by stockid, productid, date;
QUERY PLAN
Index Scan using t_stockchanges_fullindex4 on t_stockchanges
(cost=0.00..7.33 rows=1 width=46) (actual time=0.00..0.00 rows=5 loops=1)
  Index Cond: ((stockid = 1) AND (productid = 428) AND (date >=
'2004.06.01'::bpchar) AND (date <= '2004.06.29'::bpchar))
  SubPlan
    ->  Seq Scan on t_changes  (cost=0.00..1.16 rows=1 width=5) (actual
time=0.00..0.00 rows=1 loops=5)
          Filter: (id = $0)
Total runtime: 0.00 msec
========================


Recreating the t_stockchanges_fullindex I get the first result - so it is
not the case of the creation order of similar indexes or something similar.
Is there any explicit way to make the server to use an index of my choice? I
thought (from your examples) that it can be done by giving the "where" and
"order by" fields in the correct order. But now I seem making mistakes.

Thank you for your patience!

Best regards,
  -- Csaba

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27.