Re: [GENERAL] Postgres 9.6 Parallel Query
On Thu, Apr 27, 2017 at 6:38 PM, basti <mailingl...@unix-solution.de> wrote: > Hello, > > is there a recomment to set max_worker_processes and > max_parallel_workers_per_gather per cpu core or thread? > This largely depends on what type of processing is done by parallel workers, e.g. if the task is I/O bound then having workers equal to h/w threads would be beneficial since less time will spent in CPU, however if the task is CPU bound then workers more than cpu cores is not likely to give much performance improvement as they'll be contending for CPU cycles. -- Regards, Rafia Sabih EnterpriseDB: http://www.enterprisedb.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is this functional index not used?
On Mon, Mar 20, 2017 at 10:58 PM, ibeq GmbH <i...@ibeq.com> wrote:
> Given a country table and an order table:
>
>
>
> CREATE TABLE g.country
>
> (
>
> -- inherited from table g.standard: oid uuid NOT NULL,
>
> -- … some more columns inherited…
>
> lisocode integer NOT NULL, -- Numeric ISO 3166 code
>
> szisoalpha2 character varying(2), -- The 2 letter country code
>
> szisoalpha3 character varying(3), -- The 3 letter country code
>
> szisonum3 character varying(3), -- The NUMERIC country code with leading
> zeros
>
> -- a few more columns here
>
> CONSTRAINT country_pkey PRIMARY KEY (oid)
>
> ) INHERITS (g.standard)
>
> WITH (
>
> OIDS=FALSE
>
> );
>
>
>
> This table is filled with ISO 3166 country codes.
>
>
>
> The order table:
>
>
>
> CREATE TABLE t."order"
>
> (
>
> -- inherited from table g.standard: oid uuid NOT NULL,
>
> -- … some more columns inherited…
>
> szxmladdress text,
>
> -- many more columns in this table
>
> CONSTRAINT country_pkey PRIMARY KEY (oid)
>
> ) INHERITS (g.standard)
>
> WITH (
>
> OIDS=FALSE
>
> );
>
>
>
>
>
> A typical entry in t."order".szxmladdress looks like
>
>
>
>
>
> ae0eb84f-9b8b-4fef-b87a-d6757bdfeaf9
>
> 0bbdb48c-21c7-429e-944e-
> 59a4d9ace9d5
>
> Hauptstraße
>
>
>
>
>
>
>
> No XML field in the order table exceeds 2kB.
>
>
>
> Getting the 2 letter country code from the xml address by this function:
>
>
>
> CREATE OR REPLACE FUNCTION g.getxmladdresscountryid(xaddr text)
>
> RETURNS uuid AS
>
> $BODY$BEGIN
>
> RETURN (SELECT oID FROM G.Country WHERE szIsoAlpha2 =
> array_to_string(xpath('/address/@country', xAddr::xml), '') ORDER BY
> lIsoCode LIMIT 1);
>
> END$BODY$
>
> LANGUAGE plpgsql IMMUTABLE;
>
>
>
> I know that this function is not really IMMUTABLE but the country table is
> changing only every several years.
>
>
>
> Created a functional index on the order table:
>
>
>
> CREATE INDEX order_getxmladdresscountryid_fidx
>
> ON t."order"
>
> USING btree
>
> (g.getxmladdresscountryid(szxmladdress));
>
>
>
> Joining order and country table limiting to 10 rows uses the index:
>
>
>
> explain analyse
>
> SELECT
>
> GCountry.szISOAlpha2,
>
> GCountry.szISOAlpha3,
>
> GCountry.szISONum3
>
> from
>
> t.order torder
>
> left join G.Country GCountry ON G.GetXmlAddressCountryID(TOrder.szXmlAddress)
> = GCountry.oID
>
> limit 10
>
>
>
> Gives:
>
>
>
> Limit (cost=0.56..8.45 rows=10 width=11) (actual time=0.644..4.764
> rows=10 loops=1)
>
> -> Merge Right Join (cost=0.56..10670.45 rows=13517 width=11) (actual
> time=0.642..4.754 rows=10 loops=1)
>
> Merge Cond: (gcountry.oid = g.getxmladdresscountryid(
> torder.szxmladdress))
>
> -> Index Scan using country_pkey on country gcountry
> (cost=0.27..38.05 rows=258 width=27) (actual time=0.025..0.067 rows=32
> loops=1)
>
> -> Index Scan using order_getxmladdresscountryid_fidx on "order"
> torder (cost=0.29..7019.04 rows=13517 width=366) (actual time=0.020..0.058
> rows=10 loops=1)
>
> Planning time: 0.603 ms
>
> Execution time: 4.898 ms
>
>
>
> But when I remove the “limit 10”, the index is no longer used:
>
>
>
> Hash Left Join (cost=17.81..5397.46 rows=13517 width=11) (actual
> time=0.941..4721.372 rows=13510 loops=1)
>
> Hash Cond: (g.getxmladdresscountryid(torder.szxmladdress) =
> gcountry.oid)
>
> -> Seq Scan on "order" torder (cost=0.00..3504.17 rows=13517
> width=366) (actual time=0.011..27.542 rows=13510 loops=1)
>
> -> Hash (cost=14.58..14.58 rows=258 width=27) (actual
> time=0.427..0.427 rows=258 loops=1)
>
> Buckets: 1024 Batches: 1 Memory Usage: 23kB
>
> -> Seq Scan on country gcountry (cost=0.00..14.58 rows=258
> width=27) (actual time=0.008..0.226 rows=258 loops=1)
>
> Planning time: 0.580 ms
>
> Execution time: 4728.602 ms
>
>
>
> Event a “limit 100” does not use the index any more.
>
>
>
> However, the result of 4728 ms is almost a linear increase from 10 rows to
> 13500 rows.
>
>
>
> I played around with COST of the function between 1 and 2 and with
> several options on postgresql.conf without luck.
>
>
>
> A sequential scan on the order table alone is carried out in 15 ms.
>
>
>
> Thanks for any idea.
>
>
>
> Klaus
>
>
>
Notice the number of output rows in both the cases, when number of output
rows is low enough usage of index is beneficial otherwise it'll be costly
to have that many random accesses.
Anyhow, if you want to check the performance of an indexed plan for such a
query, try lowering down the value of random_page_cost.
--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/
Re: [GENERAL] Why so long?
On Wed, Apr 19, 2017 at 8:54 PM, Steve Clark <steve.cl...@netwolves.com>
wrote:
> Hello,
>
> I am confused. I have a table that has an incrementing primary key id.
>
> When I select max(id) from table is returns almost instantly but
> when I select min(id) from table it takes longer than I want to wait.
>
> Shouldn't postgresql be able to quickly find the minimum id value in the
> index?
>
>
> pmacct=# explain select max(id) from netflow;
> QUERY
> PLAN
>
> --
> Result (cost=1.13..1.14 rows=1 width=0)
>InitPlan 1 (returns $0)
> -> Limit (cost=0.71..1.13 rows=1 width=8)
>-> Index Only Scan Backward using netflow_pkey on netflow
> (cost=0.71..3799108784.10 rows=9123246080 width=8)
> Index Cond: (id IS NOT NULL)
> (5 rows)
>
> pmacct=# explain select min(id) from netflow;
>QUERY
> PLAN
>
> -
> Result (cost=1.13..1.14 rows=1 width=0)
>InitPlan 1 (returns $0)
> -> Limit (cost=0.71..1.13 rows=1 width=8)
>-> Index Only Scan using netflow_pkey on netflow
> (cost=0.71..3799108784.10 rows=9123246080 width=8)
> Index Cond: (id IS NOT NULL)
> (5 rows)
>
> pmacct=# \timing
> Timing is on.
> pmacct=# select max(id) from netflow;
> max
> -
> 17547256873
> (1 row)
>
> Time: 0.626 ms
>
>
>
> pmacct=# select min(id) from netflow;
> ^CCancel request sent
> ERROR: canceling statement due to user request
> Time: 339114.334 ms
>
>
>Table "public.netflow"
> Column |Type
> | Modifiers
> +-+-
>
> id | bigint | not null default
> nextval('netflow_id_seq'::regclass)
> agent_id | bigint |
> bytes | bigint |
> stamp_inserted | timestamp without time zone | not null default
> '0001-01-01 00:00:00'::timestamp without time zone
> stamp_updated | timestamp without time zone |
> packets| integer | default 0
> port_src | integer | default 0
> port_dst | integer | default 0
> ip_proto | smallint| default 0
> tos| smallint| default 0
> ip_src | inet| not null default
> '0.0.0.0'::inet
> ip_dst | inet| not null default
> '0.0.0.0'::inet
> Indexes:
> "netflow_pkey" PRIMARY KEY, btree (id)
> "netflow_ts_key" btree (stamp_inserted)
> "netflow_tsu_idx" btree (stamp_updated)
> Triggers:
> netflow_import AFTER INSERT OR UPDATE ON netflow FOR EACH STATEMENT
> EXECUTE PROCEDURE netflow_update()
>
>
Is this some one-time event or you could reproduce it consistently?
--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/
[GENERAL] "Re: Question about grant create on database and pg_dump/pg_dumpall"
On Tue, Jul 5, 2016 at 06:39 AM, Haribabu Kommi kommi(dot)haribabu(at)gmail(dot)com wrote: Still i feel the GRANT statements should be present, as the create database statement is generated only with -C option. So attached patch produces the GRANT statements based on the -x option. The attached patch does the job fine. However, I am a little skeptical about this addition, since, it is clearly mentioned in the documentation of pg_dump that it would not restore global objects, then why expecting this. Adding this makes pg_dump -C somewhat special as now it is restoring these grant statements. Only if we consider the popular method of dump-restore mentioned in the thread (https://www.postgresql.org/ message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org) with pg_dumpall -g and then individual pg_dump, then it would be helpful to have this patch. Regards, Rafia Sabih EnterpriseDB:: http://www.enterprisedb.com
Re: [GENERAL] Question about "grant create on database" and pg_dump/pg_dumpall
On Tue, Jul 5, 2016 at 06:39 AM, Haribabu Kommi kommi(dot)haribabu(at)gmail(dot)com wrote: Still i feel the GRANT statements should be present, as the create database statement is generated only with -C option. So attached patch produces the GRANT statements based on the -x option. The attached patch does the job fine. However, I am a little skeptical about this addition, since, it is clearly mentioned in the documentation of pg_dump that it would not restore global objects, then why expecting this. Adding this makes pg_dump -C somewhat special as now it is restoring these grant statements. Only if we consider the popular method of dump-restore mentioned in the thread ( https://www.postgresql.org/message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org) with pg_dumpall -g and then individual pg_dump, then it would be helpful to have this patch.
