>> 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@sss.pgh.pa.us>
24.08.2004 18:52
An
Silvio Matthes <silvio.matthes@xcom.de>
Kopie
pgsql-general@postgresql.org
Thema
Re: [GENERAL] select ... where <column>='' does a seqscan [auf Viren [auf Viren geprueft]
Silvio Matthes <silvio.matthes@xcom.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.