pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Poor 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>
  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 16:23  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 16:58  Pallav Kalva <pkalva@deg.cc>
  parent: 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 17:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 18:38  Pallav Kalva <pkalva@deg.cc>
  parent: 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 19:48  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 19:57  Pallav Kalva <pkalva@deg.cc>
  parent: 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 20:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 20:58  Pallav Kalva <pkalva@deg.cc>
  parent: 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 21:34  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 21:57  Pallav Kalva <pkalva@deg.cc>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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