Re: select ... where <column>='' does a seqscan [auf

>> But in my opinion with the multicolumn index in mind the server should
do
>> a index scan, because there are only 7 rows with param_name='KUNDEN_NR'

>> and param_value=''?!?

>We do not have any cross-column statistics at the moment, so the planner
>is unable to recognize the potential win here. Note the poor estimate
>of the number of matching rows in your EXPLAIN result.

So would using the index in this case improve the performance?
In short tests it seems so. But I'm running into issues that postmaster
seems to cache the results.
Is there a way to switch of caching the results (even stopping and
restarting the service on win32 did not bring the desired result...)?

Is it planned to put this feature (cross-column statistics) in postgresQL
in the future? What version could it be?

>I think though that it might help to put param_name first in the
>multicolumn index.

it helps, but not much, the performance-gain of the multicolumn index is
20%-80%. the planner still wants a seqscan, so we're talking about 50-60s.
If forced to indexscan, the time drops to 70-180ms!
Is it possible to force the planner to use an index on a
per-statement-base, so without using the set enable_seqscan-command?

Kind Regards,

Silvio Matthes

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
24.08.2004 18:52

An
Silvio Matthes <silvio(dot)matthes(at)xcom(dot)de>
Kopie
pgsql-general(at)postgresql(dot)org
Thema
Re: [GENERAL] select ... where <column>='' does a seqscan [auf Viren [auf
Viren geprueft]

Silvio Matthes <silvio(dot)matthes(at)xcom(dot)de> writes:
> But in my opinion with the multicolumn index in mind the server should
do
> a index scan, because there are only 7 rows with param_name='KUNDEN_NR'
> and param_value=''?!?

We do not have any cross-column statistics at the moment, so the planner
is unable to recognize the potential win here. Note the poor estimate
of the number of matching rows in your EXPLAIN result.

I think though that it might help to put param_name first in the
multicolumn index.

regards, tom lane