Skip site navigation (1) Skip section navigation (2)

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

From: Silvio Matthes <silvio(dot)matthes(at)xcom(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: select ... where <column>='' does a seqscan [auf
Date: 2004-08-25 08:23:20
Message-ID: OF53CB97BF.1033CF58-ONC1256EFB.002BDD72-C1256EFB.002E14F4@xcom.de (view raw or flat)
Thread:
Lists: pgsql-general
>> 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

In response to

pgsql-general by date

Next:From: Richard HuxtonDate: 2004-08-25 08:30:54
Subject: Re: Missing FROM clause
Previous:From: Magnus HaganderDate: 2004-08-25 08:11:58
Subject: Re: Missing FROM clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group