Обсуждение: Query performance issue with 8.0.0beta1

7.4.2
>  Aggregate  (cost=46817.89..46817.89 rows=1 width=0) (actual time=401.216..401.217 rows=1 loops=1)
>    ->  Index Scan using snsdata_codpar on "SNS_DATA"  (cost=0.00..46817.22 rows=268 width=0) (actual
time=165.948..400.258rows=744 loops=1) 
>          Index Cond: (("Cod_Par")::text = '17476'::text)
>          Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND
("Data_Arrivo_Campione"<= '2004-01-31 23:59:59'::timestamp without time zone)) 
>  Total runtime: 401.302 ms
>
Row counts are out by a factor of 3, on the low side. so the planner will guess index is better, which it is.

> ***while on 8.0.0***
>  Aggregate  (cost=93932.91..93932.91 rows=1 width=0) (actual time=14916.371..14916.371 rows=1 loops=1)
>    ->  Seq Scan on "SNS_DATA"  (cost=0.00..93930.14 rows=1108 width=0) (actual time=6297.152..14915.330 rows=744
loops=1)
>          Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND
("Data_Arrivo_Campione"<= '2004-01-31 23:59:59'::timestamp without time zone) AND (("Cod_Par")::text = '17476'::text)) 
>  Total runtime: 14916.935 ms
Planner guesses that 1108 row should be returned, which is out by less, but on the high side.
Big question is given there are 2M rows, why does returning 1108 rows, less than 1% result in a sequence scan.
Usually the selectivity on the index is bad, try increasing the stats target on the column.

I know 8.0 has new stats anaylsis code, which could be effecting how it choses the plan. But it would still
require a good amount of stats to get it to guess correctly.

Increase stats and see if the times improve.

>
> And I if disable the seqscan
> SET enable_seqscan = false;
>
> I get the following:
>
> Aggregate  (cost=158603.19..158603.19 rows=1 width=0) (actual time=4605.862..4605.863 rows=1 loops=1)
>    ->  Index Scan using snsdata_codpar on "SNS_DATA"  (cost=0.00..158600.41 rows=1108 width=0) (actual
time=2534.422..4604.865rows=744 loops=1) 
>          Index Cond: (("Cod_Par")::text = '17476'::text)
>          Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND
("Data_Arrivo_Campione"<= '2004-01-31 23:59:59'::timestamp without time zone)) 
>  Total runtime: 4605.965 ms
>
> The total runtime is bigger (x10 !!) than the old one.
Did you run this multiple times, or is this the first time.  If it had to get the data off disk it will be slower.
Are you sure that it's coming from disk in this and the 7.4 case? or both from memory.
If 7.4 is from buffer_cache, or kernel_cache, and 8.0 is from disk you are likely to get A LOT slower.

>
> The memory runtime parameters are
> shared_buffer = 2048
> work_mem = sort_mem = 2048
>
[ snip ]

> The table has 2M of records
> Can it be a datatype conversion issue?
That should not be an issue in 8.0, at least for the simple type conversions.  like int8 to int4.
I'm not 100% sure which ones were added, and which were not, but the query appears to cast everything correctly anyway.

> Can it be depend on the the type of restore (with COPY commands)?
Shouldn't and VACUUM FULL ANALYZE will make the table as small as possible.  The row order may be different
on disk, but the planner won't know that, and it's a bad plan causing the problem.

> I have no idea.
>
> Thanks in advance!
> Reds
>
Regards

Russell Smith.

Is it possible (to mix two threads) that you had CLUSTERed the table on the old database in a way that retrieved the
recordsin this query faster? 

Stefano,

> Hi, I have just installed 8.0.0beta1 and I noticed that some query are
> slower than 7.4.2 queries.

Seems unlikely.   How have you configured postgresql.conf?    DID you
configure it for the 8.0 database?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Server HP: Intel(R) Pentium(R) 4 CPU 2.26GHz
RAM 1GB
OS: RedHat 8
And the disk:

    kernel: megaide: driver version 05.04f (Date: Nov 11, 2002; 18:15 EST)
    kernel: megaide: bios version 02.06.07221605
    kernel: megaide: LD 0 RAID1 status=ONLINE sectors=156297343
capacity=76317 MB drives=2
    kernel: scsi0 : LSI Logic MegaIDE RAID BIOS Version 2.6.07221605, 8
targs 1 chans 8 luns
    kernel:   Vendor: LSILOGIC  Model:  LD  0  IDERAID   Rev:
    kernel:   Type:   Direct-Access                      ANSI SCSI revision:
02

Thanks.
RedS


----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Stefano Bonnin" <stefano.bonnin@comai.to>
Sent: Monday, August 30, 2004 6:54 PM
Subject: Re: [PERFORM] Query performance issue with 8.0.0beta1


> Stefano,
>
> > This is my postgres.conf, I have changed only the work_mem and
> > shared_buffers parameters.
>
> And not very much, I see.
>
> > >DID you
> > > configure it for the 8.0 database?
> >
> > What does it mean? Is in 8.0 some important NEW configation parameter ?
>
> Well, there are but they shouldn't affect your case.     However, there
are a
> lot of other settings that need to be adjusted.   Please post your
hardware
> plaform information:  OS, CPU, RAM, and disk setup.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>