pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPoor Performance on Postgres 8.0
11+ messages / 2 participants
[nested] [flat]
* Poor Performance on Postgres 8.0
@ 2005-01-28 15:15 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Pallav Kalva @ 2005-01-28 15:15 UTC (permalink / raw)
To: pgsql-performance
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: attribute table has 200k records, string table has 190 records
\d common.attribute
Table "common.attribute"
Column | Type |
Modifiers
----------------+-----------------------------+-------------------------------------------------------
attributeid | integer | not null default
nextval('COMMON.ATTRIBUTESEQ'::text)
fknamestringid | integer | not null
stringvalue | text |
integervalue | integer |
numericvalue | numeric(14,2) |
datevalue | timestamp without time zone |
booleanvalue | boolean |
bigstringvalue | text |
Indexes:
"pk_attribute_attributeid" primary key, btree (attributeid)
"uk_attribute_fkstringid_stringvalue_integervalue_numericvalue_d"
unique, btree (fknamestringid, stringvalue, integervalue, numericvalue,
datevalue)
"idx_attribute_fknamestringid" btree (fknamestringid)
Foreign-key constraints:
"fk_attribute_string" FOREIGN KEY (fknamestringid) REFERENCES
common.string(stringid)
\d common.string
Table "common.string"
Column | Type | Modifiers
----------+---------+----------------------------------------------------
stringid | integer | not null default nextval('COMMON.STRINGSEQ'::text)
value | text |
Indexes:
"pk_string_stringid" primary key, btree (stringid)
Query
select attribute0_.attributeid as attribut1_, attribute0_.stringvalue as
stringva2_,
attribute0_.bigStringvalue as bigStrin3_, attribute0_.integervalue
as integerv4_,
attribute0_.numericvalue as numericv5_, attribute0_.datevalue as
datevalue,
attribute0_.booleanvalue as booleanv7_, attribute0_.fknamestringid
as fknamest8_
from common.attribute attribute0_, common.string text1_
where (text1_.value='squareFeet' and
attribute0_.fknamestringid=text1_.stringid)
and (numericValue='775.0')
Explain Analyze from 7.4
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..501.96 rows=1 width=100) (actual
time=127.420..135.914 rows=1 loops=1)
-> Seq Scan on string text1_ (cost=0.00..12.31 rows=2 width=4)
(actual time=68.421..68.466 rows=1 loops=1)
Filter: (value = 'squareFeet'::text)
-> Index Scan using idx_attribute_fknamestringid on attribute
attribute0_ (cost=0.00..244.81 rows=1 width=100) (actual
time=58.963..67.406 rows=1 loops=1)
Index Cond: (attribute0_.fknamestringid = "outer".stringid)
Filter: (numericvalue = 775.0)
Total runtime: 136.056 ms
Explain Analyze from 8 beta
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..5440.85 rows=1 width=109) (actual
time=27.313..440.469 rows=1 loops=1)
-> Seq Scan on attribute attribute0_ (cost=0.00..5437.82 rows=1
width=109) (actual time=26.987..440.053 rows=2 loops=1)
Filter: (numericvalue = 775.0)
-> Index Scan using pk_string_stringid on string text1_
(cost=0.00..3.02 rows=1 width=4) (actual time=0.169..0.172 rows=0 loops=2)
Index Cond: ("outer".fknamestringid = text1_.stringid)
Filter: (value = 'squareFeet'::text)
Total runtime: 440.648 ms
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
@ 2005-01-28 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-01-28 16:23 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
Pallav Kalva <pkalva@deg.cc> 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. If so, try this to see what it thinks the cost of the reverse plan
is:
begin;
alter table common.string drop constraint pk_string_stringid;
explain analyze ... same query ...
rollback;
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-28 16:58 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Pallav Kalva @ 2005-01-28 16:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
>Pallav Kalva <pkalva@deg.cc> 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 both tables lately?
>
Yes
>
>3. If so, try this to see what it thinks the cost of the reverse plan
>is:
>
> 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.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
@ 2005-01-28 17:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-01-28 17:02 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
Pallav Kalva <pkalva@deg.cc> 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.
The point of the rollback is that you don't really make the pk
constraint go away. It is gone from the perspective of the EXPLAIN,
but after you rollback it's back again. Easier than rebuilding it...
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-28 18:38 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 19:48 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Pallav Kalva @ 2005-01-28 18:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..5609.19 rows=1 width=104) (actual
time=11.875..319.358 rows=1 loops=1)
Join Filter: ("outer".fknamestringid = "inner".stringid)
-> Seq Scan on attribute attribute0_ (cost=0.00..5604.76 rows=1
width=104) (actual time=11.541..318.649 rows=2 loops=1)
Filter: (numericvalue = 775.0)
-> Seq Scan on string text1_ (cost=0.00..4.41 rows=1 width=4)
(actual time=0.277..0.319 rows=1 loops=2)
Filter: (value = 'squareFeet'::text)
Total runtime: 319.496 ms
Tom Lane wrote:
>Pallav Kalva <pkalva@deg.cc> 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.
>>
>>
>
>The point of the rollback is that you don't really make the pk
>constraint go away. It is gone from the perspective of the EXPLAIN,
>but after you rollback it's back again. Easier than rebuilding it...
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
@ 2005-01-28 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 19:57 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-01-28 19:48 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
Pallav Kalva <pkalva@deg.cc> 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.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 19:48 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-28 19:57 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 20:50 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Pallav Kalva @ 2005-01-28 19:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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 width=21) (actual
time=0.283..0.322 rows=1 loops=1)
Filter: (value = 'squareFeet'::text)
Total runtime: 0.492 ms
I am not worried about this table as common.string has only 190 records,
where as the other table common.attribute which is very big (200k
records) i want it to use index scan on it . The matching column in
common.attribute table has only 175 distinct records in common.attribute
table , do you think that's the problem ? here is the full query again
select attribute0_.attributeid as attribut1_, attribute0_.stringvalue as
stringva2_,
attribute0_.bigStringvalue as bigStrin3_, attribute0_.integervalue
as integerv4_,
attribute0_.numericvalue as numericv5_, attribute0_.datevalue as
datevalue,
attribute0_.booleanvalue as booleanv7_, attribute0_.fknamestringid
as fknamest8_
from common.attribute attribute0_, common.string text1_
where (text1_.value='squareFeet' and
attribute0_.fknamestringid=text1_.stringid)
and (numericValue='775.0')
Tom Lane wrote:
>Pallav Kalva <pkalva@deg.cc> 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.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 19:48 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 19:57 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
@ 2005-01-28 20:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 20:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-01-28 20:50 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
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_stats
where tablename = 'attribute' and attname = 'fknamestringid';
It would be interesting to see the same for your 7.4 installation too.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 19:48 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 19:57 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 20:50 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-28 20:58 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 21:34 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Pallav Kalva @ 2005-01-28 20:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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 |
histogram_bounds | correlation
------------+-----------+----------------+-----------+-----------+------------+-----------------------------------------------------+-------------------------------------------------------------------------------------+----------------------------------------------------------+-------------
common | attribute | fknamestringid | 0 | 4
| 124 | {2524,2434,2523,2599,2595,2592,2596,2528,2586,2446} |
{0.132333,0.13,0.0766667,0.0373333,0.0366667,0.0333333,0.031,0.029,0.0263333,0.019}
| {2433,2441,2455,2462,2473,2479,2484,2492,2505,2574,2598} | -0.22864
(1 row)
On 8
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 |
histogram_bounds | correlation
------------+-----------+----------------+-----------+-----------+------------+-----------------------------------------------------+-----------------------------------------------------------------------------------+----------------------------------------------------------+-------------
common | attribute | fknamestringid | 0 | 4
| 80 | {2524,2434,2530,2522,2525,2523,2527,2526,2574,2531} |
{0.219333,0.199333,0.076,0.0643333,0.0616667,0.05,0.0453333,0.042,0.04,0.0286667}
| {2437,2528,2529,2538,2539,2540,2554,2562,2575,2584,2637} | 0.0274016
Tom Lane wrote:
>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_stats
>where tablename = 'attribute' and attname = 'fknamestringid';
>
>It would be interesting to see the same for your 7.4 installation too.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 19:48 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 19:57 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 20:50 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 20:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
@ 2005-01-28 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 21:57 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2005-01-28 21:34 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
Pallav Kalva <pkalva@deg.cc> 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.0643333,0.0616667,0.05,0.0453333,0.042,0.04,0.0286667}
> | {2437,2528,2529,2538,2539,2540,2554,2562,2575,2584,2637} | 0.0274016
Given those stats, the planner is going to estimate that about 1/80th of
the attribute table matches any particular fknamestringid, and that's
what's driving it away from using the indexscan. I cannot tell whether
there are indeed a couple of thousand rows joining to the 'squareFeet'
string row (in which case the condition numericValue='775.0' must be
really selective) or whether this is an outlier case that joins to just
a few attribute rows.
The slightly different stats values for 7.4 would have given it a
slightly lower value for the cost of an indexscan by
idx_attribute_fknamestringid, but certainly not as low as your original
message shows. Perhaps you have some difference in parameter settings
in your 7.4 installation --- most likely a lower random_page_cost.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Poor Performance on Postgres 8.0
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 19:48 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 19:57 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 20:50 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 20:58 ` Re: Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 21:34 ` Re: Poor Performance on Postgres 8.0 Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-01-28 21:57 ` Pallav Kalva <pkalva@deg.cc>
0 siblings, 0 replies; 11+ messages in thread
From: Pallav Kalva @ 2005-01-28 21:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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 24msec.
Also, i tried to see the matching id for squarefeet in attribute table
there are 800 some records in attribute table for 8 version and 700
something in 7.4 version.
Tom Lane wrote:
>Pallav Kalva <pkalva@deg.cc> 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.0643333,0.0616667,0.05,0.0453333,0.042,0.04,0.0286667}
>>| {2437,2528,2529,2538,2539,2540,2554,2562,2575,2584,2637} | 0.0274016
>>
>>
>
>Given those stats, the planner is going to estimate that about 1/80th of
>the attribute table matches any particular fknamestringid, and that's
>what's driving it away from using the indexscan. I cannot tell whether
>there are indeed a couple of thousand rows joining to the 'squareFeet'
>string row (in which case the condition numericValue='775.0' must be
>really selective) or whether this is an outlier case that joins to just
>a few attribute rows.
>
>The slightly different stats values for 7.4 would have given it a
>slightly lower value for the cost of an indexscan by
>idx_attribute_fknamestringid, but certainly not as low as your original
>message shows. Perhaps you have some difference in parameter settings
>in your 7.4 installation --- most likely a lower random_page_cost.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2005-01-28 21:57 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-28 15:15 Poor Performance on Postgres 8.0 Pallav Kalva <pkalva@deg.cc>
2005-01-28 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 16:58 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 17:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 18:38 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 19:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 19:57 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 20:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 20:58 ` Pallav Kalva <pkalva@deg.cc>
2005-01-28 21:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-28 21:57 ` Pallav Kalva <pkalva@deg.cc>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox