Is my text_pattern_ops index working for a LIKE prefix search with column reference?
hi, I created an index with text_pattern_ops because I want fast prefix search [1] [2]. CREATE INDEX ON mytable USING BTREE (state, city, address text_pattern_ops); (Note it's composite - I'm looking for prefixes on the address part only.) I can see that it "works" (purely uses the index) for prefix searches without column references: # explain select 1 from mytable where state = 'x' and city = 'x' and address like 'asd%'; QUERY PLAN -- Index Only Scan using mytable_state_city_house_number_and_s_idx1 on mytable (cost=0.41..4.44 rows=1 width=4) Index Cond: ((state = 'x'::text) AND (city = 'x'::text) AND (address ~>=~ 'asd'::text) AND (address ~<~ 'ase'::text)) Filter: (address ~~ 'asd%'::text) (3 rows) ...but I don't think it's working when I use a column reference || '%'... I say that because "address" no longer appears in the "Index Cond": # explain select (select 1 from mytable where state = 'x' and city = 'x' and address like generate_series.generate_series::text || '%') t2 from generate_series(0, 10); QUERY PLAN -- Function Scan on generate_series (cost=0.00..4455.00 rows=1000 width=4) SubPlan 1 -> Index Only Scan using mytable_state_city_house_number_and_s_idx1 on mytable (cost=0.41..4.44 rows=1 width=4) Index Cond: ((state = 'x'::text) AND (city = 'x'::text)) Filter: (address ~~ ((generate_series.generate_series)::text || '%'::text)) (5 rows) Any advice? I'm on 10.3. Also, has this changed between 9.6 and 10.3? And... maybe I'm asking the wrong question, how can I do a fast search on state, city, address prefix? Thanks, Seamus [1] http://blog.cleverelephant.ca/2016/08/pgsql-text-pattern-ops.html [2] https://blog.2ndquadrant.com/text-search-strategies-in-postgresql/
Too many BitmapAnds in the wild
hey, We see a fair number of incorrectly chosen BitmapAnd plans in the wild at Faraday... enough that googling the problem ends up at our old posts to this mailing list 😀. An attractive solution was proposed by Jeff Janes [1] - *cost += 0.1 * cpu_operator_cost * path->rows; + *cost += 6 * cpu_operator_cost * path->rows; It appears this constant hasn't been changed for 7 years [2]. Is there any reason not to at least adjust this up by a factor of 2? 10? If Jeff is right and 6 is closer to ideal, then 0.1 must be _way_ too low? Thanks, Seamus [1] https://www.postgresql.org/message-id/flat/CAMkU%3D1yV7WQLetrCVPqn%3DdTPdNzW3JD29ZsK0zJmgzO2tdcx-Q%40mail.gmail.com#aa3b3316cbe44fb3fd913ce019a86bb8 [2] https://github.com/postgres/postgres/blame/master/src/backend/optimizer/path/costsize.c#L1056 -- Seamus Abshere, SCEA +598 9954 9954 https://www.faraday.io https://github.com/seamusabshere https://linkedin.com/in/seamusabshere
Why is Postgres only using 8 cores for partitioned count?
hi, How can I convince Postgres to use more than 8 cores? I've got an r6gd.16xlarge with 64 vCpus and 512gb RAM running Postgres 13.1 on Ubuntu 20.04. CREATE TABLE tbl ( [...] ) PARTITION BY HASH (address_key); It has 64 partitions - e.g. FOR VALUES WITH (modulus 64, remainder 0) etc. We're running `SELECT COUNT(*) FROM tbl`. I've watched top and I never see more than 8 cores going 100%. Here is my (admittedly ridiculous) postgresql.conf: checkpoint_completion_target = 0.9 data_directory='/tank/postgresql/13/main' default_statistics_target = 100 effective_cache_size = 381696MB effective_io_concurrency = 200 enable_partition_pruning=on enable_partitionwise_aggregate=on enable_partitionwise_join=on listen_addresses='*' maintenance_work_mem = 2GB max_connections = 200 max_parallel_maintenance_workers = 4 max_parallel_workers = 512 max_parallel_workers_per_gather = 512 max_wal_size = 4GB max_worker_processes = 512 min_wal_size = 1GB random_page_cost = 1.1 shared_buffers = 127232MB shared_preload_libraries = 'cstore_fdw' synchronous_commit=off wal_buffers = 16MB work_mem = 1628560kB Best, Seamus -- Seamus Abshere, SCEA https://faraday.ai https://github.com/seamusabshere https://linkedin.com/in/seamusabshere
Re: Why is Postgres only using 8 cores for partitioned count? [Parallel Append]
hi, I've traced this back to the formula for Parallel Append workers - log2(partitions). The comment from Robert says: (src/backend/optimizer/path/allpaths.c) /* * If the use of parallel append is permitted, always request at least * log2(# of children) workers. In my case, every partition takes 1 second to scan, I have 64 cores, I have 64 partitions, and the wall time is 8 seconds with 8 workers. I assume that if it it planned significantly more workers (16? 32? even 64?), it would get significantly faster (even accounting for transaction cost). So why doesn't it ask for more? Note that I've set max_parallel_workers=512, etc. (postgresql.conf in my first message). Here are full plans https://gist.github.com/seamusabshere/1c1d869ee24145689f45e4124d8730d3 Best, Seamus On Fri, Feb 12, 2021, at 4:50 PM, Seamus Abshere wrote: > hi, > > How can I convince Postgres to use more than 8 cores? > > I've got an r6gd.16xlarge with 64 vCpus and 512gb RAM running Postgres > 13.1 on Ubuntu 20.04. > > CREATE TABLE tbl ( > [...] > ) PARTITION BY HASH (address_key); > > It has 64 partitions - e.g. FOR VALUES WITH (modulus 64, remainder 0) etc. > > We're running `SELECT COUNT(*) FROM tbl`. > > I've watched top and I never see more than 8 cores going 100%. > > Here is my (admittedly ridiculous) postgresql.conf: > > checkpoint_completion_target = 0.9 > data_directory='/tank/postgresql/13/main' > default_statistics_target = 100 > effective_cache_size = 381696MB > effective_io_concurrency = 200 > enable_partition_pruning=on > enable_partitionwise_aggregate=on > enable_partitionwise_join=on > listen_addresses='*' > maintenance_work_mem = 2GB > max_connections = 200 > max_parallel_maintenance_workers = 4 > max_parallel_workers = 512 > max_parallel_workers_per_gather = 512 > max_wal_size = 4GB > max_worker_processes = 512 > min_wal_size = 1GB > random_page_cost = 1.1 > shared_buffers = 127232MB > shared_preload_libraries = 'cstore_fdw' > synchronous_commit=off > wal_buffers = 16MB > work_mem = 1628560kB > > Best, > Seamus > > > -- > Seamus Abshere, SCEA > https://faraday.ai > https://github.com/seamusabshere > https://linkedin.com/in/seamusabshere
700% faster (was: Re: Why is Postgres only using 8 cores for partitioned count? [Parallel append])
hi, Apologies for the self-replying, but I made it 700% faster by compiling from source and forcing the parallel_workers from 7 to 96. If this weren't a partitioned table, I could just do `alter table test_3pd_cstore_partitioned set (parallel_workers = 96)` - but that isn't currently supported. diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index cd3fdd259c..0057a69d4e 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -1544,6 +1544,9 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, max_parallel_workers_per_gather); Assert(parallel_workers > 0); + // force a crazy parallelism + parallel_workers = 96; + appendpath = create_append_path(root, rel, pa_nonpartial_subpaths, pa_partial_subpaths, NIL, NULL, parallel_workers, true, BEFORE: postgres=# explain analyze select count(*) from test_3pd_cstore_partitioned where age > 50; Finalize Aggregate (cost=558860.96..558860.97 rows=1 width=8) (actual time=8528.509..8538.627 rows=1 loops=1) -> Gather (cost=56918.65..558860.94 rows=7 width=8) (actual time=1863.462..8538.603 rows=64 loops=1) Workers Planned: 7 Workers Launched: 7 -> Parallel Append (cost=55918.65..557860.24 rows=1 width=8) (actual time=1877.875..8417.486 rows=8 loops=8) [...] Execution Time: 8565.734 ms AFTER: postgres=# explain analyze select count(*) from test_3pd_cstore_partitioned where age > 50; Finalize Aggregate (cost=57030.20..57030.21 rows=1 width=8) (actual time=1125.828..1129.958 rows=1 loops=1) -> Gather (cost=56611.92..57029.96 rows=96 width=8) (actual time=994.708..1129.940 rows=64 loops=1) Workers Planned: 96 Workers Launched: 96 -> Parallel Append (cost=55611.92..56020.36 rows=1 width=8) (actual time=656.027..656.051 rows=1 loops=97) [...] Execution Time: 1133.810 ms Should I try to submit a patch that adds support for "alter table test_3pd_cstore_partitioned set (parallel_workers = 96)" for partitioned tables? Best, Seamus PS. The awesome thing about this is that I'm table scanning a 270 million row, 600 column table in 1.2 seconds, which I never thought was possible. PPS. I have no idea why 96 worked better than 64 (the number of cores I have), but it did - 700% vs 400%. On Sat, Feb 13, 2021, at 7:09 PM, Seamus Abshere wrote: > hi, > > I've traced this back to the formula for Parallel Append workers - > log2(partitions). > > The comment from Robert says: (src/backend/optimizer/path/allpaths.c) > > /* >* If the use of parallel append is permitted, always request > at least >* log2(# of children) workers. > > In my case, every partition takes 1 second to scan, I have 64 cores, I > have 64 partitions, and the wall time is 8 seconds with 8 workers. > > I assume that if it it planned significantly more workers (16? 32? even > 64?), it would get significantly faster (even accounting for > transaction cost). So why doesn't it ask for more? Note that I've set > max_parallel_workers=512, etc. (postgresql.conf in my first message). > > Here are full plans > https://gist.github.com/seamusabshere/1c1d869ee24145689f45e4124d8730d3 > > Best, > Seamus > > On Fri, Feb 12, 2021, at 4:50 PM, Seamus Abshere wrote: > > hi, > > > > How can I convince Postgres to use more than 8 cores? > > > > I've got an r6gd.16xlarge with 64 vCpus and 512gb RAM running Postgres > > 13.1 on Ubuntu 20.04. > > > > CREATE TABLE tbl ( > > [...] > > ) PARTITION BY HASH (address_key); > > > > It has 64 partitions - e.g. FOR VALUES WITH (modulus 64, remainder 0) etc. > > > > We're running `SELECT COUNT(*) FROM tbl`. > > > > I've watched top and I never see more than 8 cores going 100%. > > > > Here is my (admittedly ridiculous) postgresql.conf: > > > > checkpoint_completion_target = 0.9 > > data_directory='/tank/postgresql/13/main' > > default_statistics_target = 100 > > effective_cache_size = 381696MB > > effective_io_concurrency = 200 > > enable_partition_pruning=on > > enable_partitionwise_aggregate=on > > enable_partitionwise_join=on > > listen_addresses='*' > > maintenance_work_mem = 2GB > > max_connections = 200 > > max_parallel_maintenance_workers = 4 > > max_parallel_workers = 512 > > max_parallel_workers_per_gather = 512 > > max_wal_size = 4GB > > max_worker_processes = 512 > > min_wal_size = 1GB > > random_page_cost = 1.1 > > shared_buffers = 127232MB > > shared_preload_libraries = 'cstore_fdw' > > synchronous_commit=off > > wal_buffers = 16MB > > work_mem = 1628560kB > > > > Best, > > Seamus
Re: Why is Postgres only using 8 cores for partitioned count? [Parallel Append]
Hi David, That is a great suggestion, except my partitions are foreign tables. I wonder if I could figure out a single empty partition that’s just a normal table... but I’m using hash partitioning, so I think I would lose a modulus. Best, Seamus -- Seamus Abshere, SCEA +1 (608) 772-0696 https://www.faraday.io https://github.com/seamusabshere https://linkedin.com/in/seamusabshere On Sun, Feb 14, 2021, at 4:47 AM, David Rowley wrote: > On Sun, 14 Feb 2021 at 13:15, Seamus Abshere > wrote: > > The comment from Robert says: (src/backend/optimizer/path/allpaths.c) > > > > /* > > * If the use of parallel append is permitted, always > > request at least > > * log2(# of children) workers. > > > > In my case, every partition takes 1 second to scan, I have 64 cores, I have > > 64 partitions, and the wall time is 8 seconds with 8 workers. > > > > I assume that if it it planned significantly more workers (16? 32? even > > 64?), it would get significantly faster (even accounting for transaction > > cost). So why doesn't it ask for more? Note that I've set > > max_parallel_workers=512, etc. (postgresql.conf in my first message). > > There's perhaps an argument for allowing ALTER TABLE table> SET (parallel_workers=N); to be set on partitioned tables, but > we don't currently allow it. > > What you might want to try is setting that for any of those 64 > partitions. Shortly above the code comment that you quoted above, > there's some code that finds the path for the partition with the > maximum number of parallel workers. If one of those partitions is > using, say 64 workers because you set the partitions > "parallel_workers" setting to 64, and providing you have > max_parallel_workers_per_gather set highly enough, then your Append > should get 64 workers. > > You'll need to be careful though since changing the partitions > parallel_workers may affect things for other queries too. Also, if you > were to only change 1 partition and that partition were to be pruned, > then you'd not get the 64 workers. > > David >
warning for subquery that references a table but not its columns
I almost got bit by this today: => select email from subscribed where email not in (select email from tracks); email --- (0 rows) => select email from subscribed where email not in (select tracks.email from tracks); ERROR: column tracks.email does not exist LINE 1: ... email from subscribed where email not in (select tracks.ema... (the "tracks" table doesn't have an "email" column, so the first query is just meaningless) Should there be a warning for the first query that you reference "tracks" in a subquery but don't use any columns from it? -- Seamus Abshere, SCEA https://www.faraday.io https://github.com/seamusabshere https://linkedin.com/in/seamusabshere
