Re: Need more speed from this.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: wsheldah(at)lexmark(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Need more speed from this.
Date: 2001-11-19 22:48:13
Message-ID: 23183.1006210093@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

wsheldah(at)lexmark(dot)com writes:
> Aggregate (cost=17296.84..17296.84 rows=1 width=8)
> -> Index Scan using data_type_id_idx on system_data
> (cost=0.00..17296.84 rows=1 width=8)

That's an awfully high cost for an indexscan that's only supposed to
return one row!

I'd guess that in fact the indexscan itself is returning a lot of rows,
and that the reduction to a small number of rows comes mostly from
applying the restrictions on date and server_id (which actually happens
above the index search, though you can't see the difference in EXPLAIN's
depiction). How many rows are there with data_type_id = 8?

Merely having an index is not much help if the index is unselective,
which is to say that there aren't many distinct values in the indexed
column. It would seem though that the system thinks the data_type_id
index is the best alternative it's got. Would scanning on server_id
yield a smaller number of matches in the index? How about date, or
a multicolumn index combining some of these fields?

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Lark 2001-11-19 23:51:54 xxx_seq.nextval:you don't have permissions to set sequence
Previous Message wsheldah 2001-11-19 22:17:14 Re: Need more speed from this.