Josh Berkus | 29 Sep 23:54 2014

Re: Yet another abort-early plan disaster on 9.3

On 09/26/2014 01:06 AM, Simon Riggs wrote:
> On 23 September 2014 00:56, Josh Berkus <josh <at> agliodbs.com> wrote:
> 
>> We've hashed that out a bit, but frankly I think it's much more
>> profitable to pursue fixing the actual problem than providing a
>> workaround like "risk", such as:
>>
>> a) fixing n_distinct estimation
>> b) estimating stacked quals using better math (i.e. not assuming total
>> randomness)
>> c) developing some kind of correlation stats
>>
>> Otherwise we would be just providing users with another knob there's no
>> rational way to set.
> 
> I believe this is a serious issue for PostgreSQL users and one that
> needs to be addressed.
> 
> n_distinct can be fixed manually, so that is less of an issue.

It's an issue for the 99.8% of our users who don't know what n_distinct
is, let alone how to calculate it.  Also, changing it requires an
exclusive lock on the table. Of course, you and I have been over this
issue before.

One thing I'm wondering is why our estimator is creates n_distinct as a
% so seldom.  Really, any time n_distinct is over 10K we should be
estimating a % instead.  Now, estimating that % has its own issues, but
it does seem like a peculiar quirk of our stats model.

(Continue reading)

Matúš Svrček | 28 Sep 22:24 2014
Picon

Re: after upgrade 8.4->9.3 query is slow not using index scan

Hello,
at the moment, I am not able to replicate the results posted. Explain analyze results are very similar at the
moment on both databases, query runtime is also almost same.
This has happened after re-running VACUUM ANALYZE VERBOSE. However, right after the migration, I did run
the script, which was generated by pg_upgrade script, which should have ran VACUUM on the database, the
script name is analyze_new_cluster.sh. 
Maybe after re-running vacuum analyze the statistics somehow changed?

Case closed, thank You.
--
Matúš Svrček
PlainText s.r.o. [www.plaintext.sk]
svrcek <at> plaintext.sk

----- "Victor Yegorov" <vyegorov <at> gmail.com> wrote:

> 2014-09-26 17:04 GMT+03:00 Matúš Svrček < svrcek <at> plaintext.sk > :
> 
> 
> I am having a performance issue after upgrade from 8.4.20-1 -> 9.3.5.
> 
> 
> First, make sure you have your statistics up to date — execute manual
> `VACUUM ANALYZE`.
> And then provide `EXPLAIN analyze` for 8.4 and `EXPLAIN (analyze,
> buffers)` for 9.3 output.
> 
> 
> 
> --
(Continue reading)

Burgess, Freddie | 27 Sep 00:25 2014

Re: Very slow postgreSQL 9.3.4 query

We also have in our postgresql.conf file

autovaccum = on
default_statistics_target = 100

Do you recommend any changes?

This partitioned table doti_sensor_report contains in total approximately 15 billion rows, autovaccum current has three processes that are running continuously on the box and specifically targeting this table to keep up.

What does the upgrade to 9.3.5 buy us in terms of performance improvements?

thanks Victor

Freddie

From: Victor Yegorov [vyegorov <at> gmail.com]
Sent: Friday, September 26, 2014 4:25 PM
To: Burgess, Freddie
Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query

2014-09-26 23:07 GMT+03:00 Burgess, Freddie <FBurgess <at> radiantblue.com>:
I have a cron job that updates the statistics on the "doti_sensor_report" table on the first Saturday of every month. Do you think I should re-generate these statistics more often? This table receives streaming inserts to the volume of about 350 million tuples per-month.

There's autovacuum that does the same job for you, I hope you have it enabled on your upgraded DB.
If not, then once-a-month stats is definitely not enough.

I recommend you to look into autovacuum instead of using cron and tune per-table autovacuum settings:

Default parameters will cause autovacuum to process the table if 10% (analyze) or 20% (vacuum) of the table had changed. The bigger the table,
the longer it'll take to reach the threshold. Try lowering scale factors on a per-table basis, like:

    ALTER TABLE doti_sensor_report_y2014m09 SET (autovacuum_analyze_scale_factor=0.02, autovacuum_vacuum_scale_factor=0.05);

Also, given your tables are quite big, I would recommend to increase statistics targets for commonly used columns, like:

    ALTER TABLE doti_sensor_report_y2014m09 ALTER node_date_time SET STATISTICS 1000;

Have a look at the docs on these topics and pick they way that suits you most. 


P.S. Consider upgrading to 9.3.5 also, it is a minor one: only restart is required.


--
Victor Y. Yegorov
Burgess, Freddie | 26 Sep 22:07 2014

Re: Very slow postgreSQL 9.3.4 query

I have a cron job that updates the statistics on the "doti_sensor_report" table on the first Saturday of every month. Do you think I should re-generate these statistics more often? This table receives streaming inserts to the volume of about 350 million tuples per-month.

I'll generate new stat's over the weekend, and then execute a new plan

thanks

From: Victor Yegorov [vyegorov <at> gmail.com]
Sent: Friday, September 26, 2014 3:15 PM
To: Burgess, Freddie
Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query

2014-09-26 19:17 GMT+03:00 Burgess, Freddie <FBurgess <at> radiantblue.com>:
Performance data is included in the attachment, via EXPLAIN PLAN, query takes approx 6 minutes to return count to UI.
Amount of data processed is also included in the attachment, 185 million row partition.

It looks like your statistics are off:

-> Index Scan using idx_sensor_report_query_y2014m09 on doti_sensor_report_y2014m09 this__1 (cost=0.57..137498.17 rows=3883 width=0) (actual time=168.416..348873.308 rows=443542 loops=1)

Optimizer expects to find ~ 4k rows, while in reality there're 2 orders of magnitude more rows that matches the condition.
Perhaps BitmapIndexScan could be faster here.


--
Victor Y. Yegorov
Matúš Svrček | 26 Sep 16:04 2014
Picon

after upgrade 8.4->9.3 query is slow not using index scan

Hello,
I am having a performance issue after upgrade from 8.4.20-1 -> 9.3.5. I am running on CentOS
2.6.32-431.29.2.el6.x86_64 #1 SMP Tue Sep 9 21:36:05 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux.
Upgrade was without any issues, I used pg_upgrade.

One of my queries now takes cca 100x more time than it used to. The query is:
http://pastebin.com/uUe16SkR

explain from postgre 8.4.20-1:
http://pastebin.com/r3WRHzSM

explain from postgre 9.3.5:
http://pastebin.com/hmNxFiDL

The problematic part seems to be this (postgresql 93 version):
 SubPlan 17
                             ->  Limit  (cost=8.29..8.41 rows=1 width=11)
                                   InitPlan 16 (returns $19)
                                     ->  Index Scan using t_store_info_pkey on t_store_info s_7  (cost=0.28..8.29 rows=1 width=8)
                                           Index Cond: (id = 87::bigint)
                                   ->  Nested Loop  (cost=0.00..72351.91 rows=624663 width=11)
                                         ->  Seq Scan on t_pn pn  (cost=0.00..37498.65 rows=1 width=11) <<-----!!!!
                                               Filter: ((max(w.item_ean) = ean) AND (company_fk = $19))
                                         ->  Seq Scan on t_weighting w4  (cost=0.00..28606.63 rows=624663 width=0)

this row: Seq Scan on t_pn pn  (cost=0.00..37498.65 rows=1 width=11) in 8.4 explain looks like this:
->  Index Scan using inx_pn_companyfk_ean on t_pn pn  (cost=0.00..8.64 rows=1 width=11)
                                   Index Cond: ((company_fk = $19) AND ($20 = ean))

As You can see, 8.4 is using index scan on the table, 9.3 is using seq scan. The relevant index does exist in
both databases.
So I tried to force 9.3 to use the index by:
set enable_seqscan = off;

Now explain analyze looks like this:
http://pastebin.com/kR7qr39u

the relevant problematic part is:
 SubPlan 17w.stat_count_entered IS NULL AND w.stat_weight_start IS NULL))
                             ->  Limit  (cost=9.15..9.31 rows=1 width=11)
                                   InitPlan 16 (returns $19)
                                     ->  Index Scan using t_store_info_pkey on t_store_info s_7  (cost=0.28..8.29 rows=1 width=8)
                                           Index Cond: (id = 87::bigint)
                                   ->  Nested Loop  (cost=0.85..102881.78 rows=624667 width=11)
                                         ->  Index Only Scan using int_t_weighting_coordinates on t_weighting w4  (cost=0.42..95064.99
rows=624667 <<---- !!!
                                         ->  Materialize  (cost=0.43..8.45 rows=1 width=11)
                                               ->  Index Scan using inx_pn_companyfk_ean on t_pn pn  (cost=0.43..8.45 rows=1 width=11)
                                                     Index Cond: ((company_fk = $19) AND (max(w.item_ean) = ean))

So planner is now using index scan.

Query execution time with this is around 4.2 s (roughly same as in postgre 8.4) , with enable_seqscan=on it
is around 360s (2 orders of magnitude higher than with postgre 8.4). What is interesting is, that query
cost is roughly the same in both situations.

My questions are:
 1. how to set postgresql / modify query / create some indexes / whatever, to get the same query running time in
postgresql 9.3 as I had in 8.4
 2. how is it possible for analyze to get same costs when the query running time is almost 100x higher.

Thank You for any ideas on this.
--
Matúš Svrček
svrcek <at> plaintext.sk

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Burgess, Freddie | 26 Sep 15:04 2014

Very slow postgreSQL 9.3.4 query

Help, please can anyone offer suggestions on how to speed this query up.

thanks


dotidb=# select count(*) from doti_sensor_report_y2014m09;
   count 
----------
184,888,345
(1 row)

dotidb=# \d+ doti_sensor_report_y2014m09   <-- Partition table of parent table public.doti_sensor_report

           Table "public.doti_sensor_report_y2014m09"
        Column         |            Type             | Modifiers 
-----------------------+-----------------------------+-----------
 sensor_report_uid     | bigint                      | not null
 report_type           | character varying(255)      | not null
 sensor_report_uuid    | uuid                        | 
 model_uid             | bigint                      | not null
 sensor_location       | geometry                    | not null
 node_date_time        | timestamp without time zone | 

Indexes:
    "doti_sensor_report_y2014m09_pkey" PRIMARY KEY, btree (sensor_report_uid), tablespace "doti_data_y2014"
    "idx_sensor_report_query_y2014m09" btree (model_uid, node_date_time), tablespace "doti_data_y2014"
    "sidx_sensor_report_y2014m09" gist (sensor_location) INVALID, tablespace "doti_data_y2014"   <--
set invalid to force planner to use compound index, when toggled spatial index runs in eccess of 8 minutes
Check constraints:
    "doti_sensor_report_y2014m09_node_date_time_check" CHECK (node_date_time >= '2014-09-01'::date
AND node_date_time < '2014-10-01'::date)
Inherits: doti_sensor_report
Tablespace: "doti_data_y2014"

explain (analyze,buffers) 
select count(*) as y0_ from DOTI_SENSOR_REPORT this_ 
  where this_.node_date_time between '2014-09-20 23:40:56.245'::timestamp without time zone and
'2014-09-21 07:36:47.388'::timestamp without time zone 
    and this_.model_uid=20164
     and (ST_within
(this_.sensor_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F') 
       or ST_touches (this_.sensor_location,'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'))

                                                               QUERY PLAN                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=137582.68..137582.71 rows=1 width=0) (actual time=349105.178 rows=1 loops=1)
   Buffers: shared hit=157038
   ->  Append  (cost=0.00..137572.97 rows=3885 width=0) (actual time=168.419..348986.845 rows=443542 loops=1)
         Buffers: shared hit=157038
         ->  Seq Scan on doti_sensor_report this_  (cost=0.00..0.00 rows=1 width=0) (actual time=0.002..0.002
rows=0 loops=1)
               Filter: ((node_date_time >= '2014-09-20 23:40:56.245'::timestamp without time zone) AND
(node_date_time <= '2014-09-21 07:36:47.388'::timestamp without time zone) 
			      AND (sensor_location &&
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry) 
                  AND (model_uid = 20164) 
				  AND
(_st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry,
sensor_location) 
                   OR _st_touches(sensor_location, '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
         ->  Index Scan using idx_sensor_report_query_y2014m09 on doti_sensor_report_y2014m09 this__1 
(cost=0.57..137498.17 rows=3883 width=0) (actual time=168.416..348873.308 rows=443542 loops=1)
               Index Cond: ((model_uid = 20164) AND (node_date_time >= '2014-09-20 23:40:56.245'::timestamp without
time zone) AND (node_date_time <= '2014-09-21 07:36:47.388'::timestamp without time zone))
               Filter: ((sensor_location &&
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry) 
			      AND
(_st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry,
sensor_location) 
				   OR _st_touches(sensor_location, '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))
			   Rows Removed by Filter: 3310409
               Buffers: shared hit=157038
         ->  Seq Scan on doti_sensor_report_overflow this__2  (cost=0.00..7480 rows=1 width=0) (actual
time=0.002..0.002 rows=0 loops=1)
               Filter: ((node_date_time >= '2014-09-20 23:40:56.245'::timestamp without time zone) AND
(node_date_time <= '2014-09-21 07:36:47.388'::timestamp without time zone) 
			      AND (sensor_location &&
'010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry) 
				  AND (model_uid = 20164) AND
(_st_contains('010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry,
sensor_location) 
				   OR _st_touches(sensor_location, '010300000001000000050000006787D1E89889F5BFFBA6BE01196EE53F1AF703F9588EF5BF6D9AC3A07D5FE53F0C2792E0B193F5BF6D9AC3A07D5FE53FC096C4F07198F5BFFBA6BE01196EE53F6787D1E89889F5BFFBA6BE01196EE53F'::geometry)))

 Total runtime: 349105.317 ms    <-- 5.81842 minutes unacceptable
(14 rows)

dotidb=# 


--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Emi Lu | 23 Sep 22:37 2014
Picon
Picon

Which update action quicker?

Hello list,

For a big table with more than 1,000,000 records, may I know which update is quicker please?

(1) update t1
      set c1 = a.c1
      from a
      where pk and
                 t1.c1       <> a.c1;
 ......
      update t1
      set c_N = a.c_N
      from a
      where pk and
                 t1.c_N       <> a.c_N;


(2)  update t1
      set c1 = a.c1 ,
            c2  = a.c2,
            ...
            c_N = a.c_N
     from a
     where pk AND
               (  t1.c1 <> a.c1 OR t1.c2 <> a.c2..... t1.c_N <> a.c_N)


Or other quicker way for update action?

Thank you
Emi
Ross Elliott | 23 Sep 14:21 2014

Slow query

Maybe someone can explain this. The following SQL will reproduce our issue:
DROP TABLE IF EXISTS t1 CASCADE;
CREATE TABLE t1 (name text,
                 state text);
CREATE INDEX t1_name ON t1(name);
CREATE INDEX t1_state ON t1(state);
CREATE INDEX t1_name_state ON t1(name,state);

-- Create some sample data
DO $$
DECLARE
states text[] := array['UNKNOWN', 'TODO', 'DONE', 'UNKNOWN'];
BEGIN
FOR v IN 1..200000 LOOP
  INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
  INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
  INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
  INSERT INTO t1 VALUES('user'||v, states[floor(random()*4)]);
END LOOP;
END $$;


CREATE OR REPLACE FUNCTION state_to_int(state character varying) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$BEGIN
IF state = 'UNKNOWN' THEN RETURN 0;
ELSIF state = 'TODO' THEN RETURN 1;
ELSIF state = 'DONE' THEN RETURN 2;
ELSIF state = 'NOT REQUIRED' THEN RETURN 3;
ELSE RAISE EXCEPTION 'state_to_int called with invalid state value';
END IF;
END;$$;

CREATE OR REPLACE FUNCTION int_to_state(state integer) RETURNS character varying
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$BEGIN
IF state = 0 THEN RETURN 'UNKNOWN';
ELSIF state = 1 THEN RETURN 'TODO';
ELSIF state = 2 THEN RETURN 'DONE';
ELSIF state = 3 THEN RETURN 'NOT REQUIRED';
ELSE RAISE EXCEPTION 'int_to_state called with invalid state value';
END IF;
END;$$;

-- Why is this a lot slower
explain (analyse, buffers) select name, int_to_state(min(state_to_int(state))) as status from t1 group by t1.name;

-- Than this?
explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE', 'NOT REQUIRED'])[min(
CASE state
WHEN 'UNKNOWN' THEN 0
WHEN 'TODO' THEN 1
WHEN 'DONE' THEN 2
WHEN 'NOT REQUIRED' THEN 3
END)] AS status from t1 group by t1.name;

-- This is also very much slower
explain (analyze, buffers) select name, (array['UNKNOWN', 'TODO', 'DONE', 'NOT REQUIRED'])[min(state_to_int(state))] AS status from t1 group by t1.name;

This was done on:
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

We get results like this:
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..280042.62 rows=208120 width=15) (actual time=0.076..2439.066 rows=200000 loops=1)
   Buffers: shared hit=53146
   ->  Index Scan using t1_name on t1  (cost=0.42..21931.42 rows=800000 width=15) (actual time=0.009..229.477 rows=800000 loops=1)
         Buffers: shared hit=53146
 Total runtime: 2460.860 ms
(5 rows)

                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..36012.62 rows=208120 width=15) (actual time=0.017..559.384 rows=200000 loops=1)
   Buffers: shared hit=53146
   ->  Index Scan using t1_name on t1  (cost=0.42..21931.42 rows=800000 width=15) (actual time=0.008..197.133 rows=800000 loops=1)
         Buffers: shared hit=53146
 Total runtime: 574.550 ms
(5 rows)

                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.42..228012.62 rows=208120 width=15) (actual time=0.042..2089.367 rows=200000 loops=1)
   Buffers: shared hit=53146
   ->  Index Scan using t1_name on t1  (cost=0.42..21931.42 rows=800000 width=15) (actual time=0.008..237.854 rows=800000 loops=1)
         Buffers: shared hit=53146
 Total runtime: 2111.004 ms
(5 rows)


We cannot change our table structure to reflect something more sensible. What we would really like to know is why using functions is so much slower than the unreadable method.

Regards

Ross


Josh Berkus | 23 Sep 01:56 2014

Re: Yet another abort-early plan disaster on 9.3

On 09/22/2014 06:55 AM, Merlin Moncure wrote:
> Has any progress been made on the performance farm?  The problem with
> suggestions like this (which seem pretty reasonable to me) is that
> we've got no way of quantifying the downside. 

Yeah, that's certainly an issue. The problem is that we'd need a
benchmark which actually created complex query plans.  I believe that
Mark Wong is working on TPCH-based benchmarks, so maybe we'll get that.

>  I think this is one
> example of a class of plans that are high risk.  Another one off the
> top of my head is nestloop joins based on assumed selectivity of
> multiple stacked quals.  

Yeah, that's another good example.

> About 90% of the time, my reflective
> workaround to these types of problems is to 'disable_nestloop' which
> works around 90% of the time and the result are solved with monkeying
> around with 'OFFSET 0' etc.   In the past, a GUC controlling planner
> risk has been much discussed -- maybe it's still worth considering?

We've hashed that out a bit, but frankly I think it's much more
profitable to pursue fixing the actual problem than providing a
workaround like "risk", such as:

a) fixing n_distinct estimation
b) estimating stacked quals using better math (i.e. not assuming total
randomness)
c) developing some kind of correlation stats

Otherwise we would be just providing users with another knob there's no
rational way to set.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Josh Berkus | 20 Sep 20:33 2014

Re: Yet another abort-early plan disaster on 9.3

On 09/19/2014 11:38 PM, Greg Stark wrote:
> 
> On 19 Sep 2014 19:40, "Josh Berkus" <josh <at> agliodbs.com
> <mailto:josh <at> agliodbs.com>> wrote:
>>
>> On 09/19/2014 10:15 AM, Merlin Moncure wrote:
>> > On Wed, Sep 17, 2014 at 7:11 PM, Josh Berkus <josh <at> agliodbs.com
> <mailto:josh <at> agliodbs.com>> wrote:
>> >> This is the core issue with abort-early plans; they depend on our
>> >> statistics being extremely accurate, which we know they are not. And if
>> >> they're wrong, the execution time climbs by 1000X or more.  Abort-early
>> >> plans are inherently riskier than other types of query plans.
> 
> All plans are risky if the stats are wrong. It's one of the perennial
> digressions that many postgres newcomers make to track worst case costs
> and provide a knob for planner aggressiveness but it always breaks down
> when you try to quantify the level of risk because you discover that
> even such simple things as indeed scans versus sequential scans can be
> equally risky either way.

I've had a *wee* bit more experience with query plans than most Postgres
newcomers, Greg.

While all query plan changes can result in regressions if they're bad,
there are certain kinds of plans which depend more on accurate stats
than others.  Abort-early plans are the most extreme of these.  Most
likely we should adjust the cost model for abort-early plans to take in
our level of uncertainty, especially since we *know* that our n-distinct
estimation is crap.  For example, we could increase the estimated cost
for an abort-early index scan by 10X, to reflect our weak confidence in
its correctness.

We could also probably do the same for plans which depend on column
correlation estimates being accurate.

> 
>> >> What I'm not clear on is why upgrading from 9.1 to 9.3 would bring
> about
>> >> this change.  The stats are no more than 10% different across the
>> >> version change.
> 
> There's no difference. Postgres has been estimating LIMIT costs this way
> since before I came to postgres in 7.3.

Then why is the plan different in 9.1 and 9.3 with identical stats (I
tested)?

> 
> It would be neat to have an opclass which worked like that. Which would
> amount to having prefix compression perhaps.
> 
> What plan does 9.1 come up with?

That was the "good" plan from my original post.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Josh Berkus | 19 Sep 23:40 2014

Re: query a table with lots of coulmns

On 09/19/2014 04:51 AM, Björn Wittich wrote:
> 
> I am relatively new to postgres. I have a table with 500 coulmns and
> about 40 mio rows. I call this cache table where one column is a unique
> key (indexed) and the 499 columns (type integer) are some values
> belonging to this key.
> 
> Now I have a second (temporary) table (only 2 columns one is the key of
> my cache table) and I want  do an inner join between my temporary table
> and the large cache table and export all matching rows. I found out,
> that the performance increases when I limit the join to lots of small
> parts.
> But it seems that the databases needs a lot of disk io to gather all 499
> data columns.
> Is there a possibilty to tell the databases that all these colums are
> always treated as tuples and I always want to get the whole row? Perhaps
> the disk oraganization could then be optimized?

PostgreSQL is already a row store, which means by default you're getting
all of the columns, and the columns are stored physically adjacent to
each other.

If requesting only 1 or two columns is faster than requesting all of
them, that's pretty much certainly due to transmission time, not disk
IO.  Otherwise, please post your schema (well, a truncated version) and
your queries.

BTW, in cases like yours I've used a INT array instead of 500 columns to
good effect; it works slightly better with PostgreSQL's compression.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Gmane