The random_page_cost value is same on both the versions, the only thing
difference between 7.4 version and 8 version is that 7.4 ver has 100k
less records. For, now i created index on numericvalue column on
attribute table and it used that index and it is much faster that way.
it came down to 2
Pallav Kalva <[EMAIL PROTECTED]> writes:
> On 8
> common | attribute | fknamestringid | 0 | 4
> | 80 | {2524,2434,2530,2522,2525,2523,2527,2526,2574,2531} |
> {0.219333,0.199333,0.076,0.064,0.0616667,0.05,0.045,0.042,0.04,0.0286667}
>
> | {2437,2528,2529,253
On 7.4 I get
select * from pg_stats
where tablename = 'attribute' and attname = 'fknamestringid';
schemaname | tablename |attname | null_frac | avg_width |
n_distinct | most_common_vals
|
most_common_freqs
I was able to duplicate this behavior with dummy data that had only a
few distinct values for fknamestringid --- the planner then thinks that
the index probe into attribute will match a lot of rows and hence take a
long time. Could we see your pg_stats row for fknamestringid, ie
select * from pg_
explain analyze select * from common.string text1_
where text1_.value='squareFeet';
QUERY PLAN
Seq Scan on string text1_ (cost=0.00..4.41 rows=1 wi
Pallav Kalva <[EMAIL PROTECTED]> writes:
> still doesnt make use of the index on common.attribute table .
What do you get from just plain
explain analyze select * from common.string text1_
where text1_.value='squareFeet';
I get the impression that it must think this will yield a lot of rows.
Hi Tom,
I dropped the primary key constraint and ran the explain analyze on
the same query and here is what i get seq scans on both the tables ,
still doesnt make use of the index on common.attribute table .
QUERY PLAN
-
Pallav Kalva <[EMAIL PROTECTED]> writes:
>> begin;
>> alter table common.string drop constraint pk_string_stringid;
>> explain analyze ... same query ...
>> rollback;
>>
>what do u mean by rollback exactly ? i can drop the pk constraint
> and run explain analyze and see how it behaves.
T
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
I am running this query on postgres 8 beta version and it is not
using the right index, where as if i run the same query on postgres 7.4
version it uses the right index .
1. Beta which, exactly?
Beta 4
2. Have you ANALYZEd bot
Pallav Kalva <[EMAIL PROTECTED]> writes:
> I am running this query on postgres 8 beta version and it is not
> using the right index, where as if i run the same query on postgres 7.4
> version it uses the right index .
1. Beta which, exactly?
2. Have you ANALYZEd both tables lately?
3. I
Hi Folks ,
I am running this query on postgres 8 beta version and it is not
using the right index, where as if i run the same query on postgres 7.4
version it uses the right index . Here are the explain analyze output
for both the versions.can anyone explain this ?
tks.
tables: att
11 matches
Mail list logo
