[PERFORM] correlated exists with join is slow.
Hello. Today I've found a query that I thought will be fast turned out to be slow. The problem is correlated exists with join - it does not want to make correlated nested loop to make exists check. Even if I force it to use nested loop, it materialized join uncorrelated and then filters it. It's OK when exists does not have join. Also good old left join where X=null works fast. Note that I could see same problem for both exists and not exists. Below is test case (tested on 9.1.4) with explains. create temporary table o(o_val,c_val) as select v, v/2 from generate_series(1,100) v; create temporary table i(o_ref, l_ref) as select generate_series(1,100), generate_series(1,10); create temporary table l(l_val, l_name) as select v, 'n_' || v from generate_series(1,10) v; create index o_1 on o(o_val); create index o_2 on o(c_val); create index i_1 on i(o_ref); create index i_2 on i(l_ref); create index l_1 on l(l_val); create index l_2 on l(l_name); analyze o; analyze i; analyze l; explain analyze select 1 from o where not exists (select 1 from i join l on l_ref = l_val where l_name='n_2' and o_ref=o_val) and c_val=33; -- http://explain.depesz.com/s/Rvw explain analyze select 1 from o where not exists (select 1 from i join l on l_ref = l_val where l_val=2 and o_ref=o_val) and c_val=33; -- http://explain.depesz.com/s/fVHw explain analyze select 1 from o where not exists (select 1 from i where l_ref=2 and o_ref=o_val) and c_val=33; -- http://explain.depesz.com/s/HgN explain analyze select 1 from o left join i on o_ref=o_val left join l on l_ref = l_val and l_name='n_2' where o_ref is null and c_val=33; -- http://explain.depesz.com/s/mLA set enable_hashjoin=false; explain analyze select 1 from o where not exists (select 1 from i join l on l_ref = l_val where l_name='n_2' and o_ref=o_val) and c_val=33; -- http://explain.depesz.com/s/LYu rollback; -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] correlated exists with join is slow.
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= writes: > Today I've found a query that I thought will be fast turned out to be slow. > The problem is correlated exists with join - it does not want to make > correlated nested loop to make exists check. 9.2 will make this all better. These are exactly the type of case where you need the "parameterized path" stuff. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] correlated exists with join is slow.
Tom Lane wrote: > 9.2 will make this all better. These are exactly the type of case > where you need the "parameterized path" stuff. Yeah, with HEAD on my workstation all of these queries run in less than 0.1 ms. On older versions, I'm seeing times like 100 ms to 150 ms for the slow cases. So in this case, parameterized paths allow an improvement of more than three orders of magnitude. :-) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] correlated exists with join is slow.
Glad to hear postgresql becomes better and better :) 2012/6/18 Tom Lane > =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= writes: > > Today I've found a query that I thought will be fast turned out to be > slow. > > The problem is correlated exists with join - it does not want to make > > correlated nested loop to make exists check. > > 9.2 will make this all better. These are exactly the type of case where > you need the "parameterized path" stuff. > >regards, tom lane > -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] Expected performance of querying 5k records from 4 million records?
Thanks for the help, Jeff and Josh. It looks reclustering the multi-column index might solve things. For my particular query, because I'm getting a range of records back, it makes sense that reclustering will benefit me if I have a slow disk even if I had expected that the indices would be sufficient . I now need to make sure that the speed up I'm seeing is not because things have been cached. That being said, here's what I have: 2CPUs, 12 physical cores, hyperthreaded (24 virtual cores), 2.67Ghz 96G RAM, 80G available to dom0 CentOS 5.8, Xen 3Gbps SATA (7200 RPM, Hitachi ActiveStar Enterprise Class) So, I have lots of RAM, but not necessarily the fastest disk. default_statistics_target = 50 # pgtune wizard 2011-03-16 maintenance_work_mem = 1GB # pgtune wizard 2011-03-16 constraint_exclusion = on # pgtune wizard 2011-03-16 checkpoint_completion_target = 0.9 # pgtune wizard 2011-03-16 effective_cache_size = 24GB # pgtune wizard 2011-03-16 work_mem = 192MB # pgtune wizard 2011-03-16 wal_buffers = 8MB # pgtune wizard 2011-03-16 checkpoint_segments = 128 # pgtune wizard 2011-03-16, amended by am, 30may2011 shared_buffers = 4GB # pgtune wizard 2011-03-16 max_connections = 100 # pgtune wizard 2011-03-16: 80, bumped up to 100 max_locks_per_transaction = 1000 I didn't know about explain (analyze,buffers). Very cool. So, based on your advice, I ran it and here's what I found: 1st time I ran the query: QUERY PLAN --- Bitmap Heap Scan on data_part_213 (cost=113.14..13725.77 rows=4189 width=16) (actual time=69.807..2763.174 rows=5350 loops=1) Recheck Cond: ((data_id >= 50544630) AND (data_id <= 50549979)) Filter: ((dataset_id = 213) AND (stat_id = 6)) Buffers: shared read=4820 -> Bitmap Index Scan on data_unq_213 (cost=0.00..112.09 rows=5142 width=0) (actual time=51.918..51.918 rows=5350 loops=1) Index Cond: ((data_id >= 50544630) AND (data_id <= 50549979)) Buffers: shared read=19 Total runtime: 2773.099 ms (8 rows) the second time I run the query it's very fast, since all the buffered read counts have turned into hit counts showing I'm reading from cache (as I expected): QUERY PLAN Bitmap Heap Scan on data_part_213 (cost=113.14..13725.77 rows=4189 width=16) (actual time=1.661..14.376 rows=5350 loops=1) Recheck Cond: ((data_id >= 50544630) AND (data_id <= 50549979)) Filter: ((dataset_id = 213) AND (stat_id = 6)) Buffers: shared hit=4819 -> Bitmap Index Scan on data_unq_213 (cost=0.00..112.09 rows=5142 width=0) (actual time=0.879..0.879 rows=5350 loops=1) Index Cond: ((data_id >= 50544630) AND (data_id <= 50549979)) Buffers: shared hit=18 Total runtime: 20.232 ms (8 rows) Next, I tried reclustering a partition with the multicolumn-index. the big things is that the read count has dropped dramatically! Index Scan using data_part_214_dataset_stat_data_idx on data_part_214 (cost=0.00..7223.05 rows=4265 width=16) (actual time=0.093..7.251 rows=5350 loops=1) Index Cond: ((dataset_id = 214) AND (data_id >= 50544630) AND (data_id <= 50549979) AND (stat_id = 6)) Buffers: shared hit=45 read=24 Total runtime: 12.929 ms (4 rows) second time: -- Index Scan using data_part_214_dataset_stat_data_idx on data_part_214 (cost=0.00..7223.05 rows=4265 width=16) (actual time=0.378..7.696 rows=5350 loops=1) Index Cond: ((dataset_id = 214) AND (data_id >= 50544630) AND (data_id <= 50549979) AND (stat_id = 6)) Buffers: shared hit=68 Total runtime: 13.511 ms (4 rows) So, it looks like clustering the index appropriately fixes things! Also, I'll recreate the index switching the order to (dataset_id, stat_id,data_id) thanks! On Fri, Jun 15, 2012 at 11:20 AM, Jeff Janes wrote: > On Fri, Jun 15, 2012 at 9:17 AM, Anish Kejariwal > wrote: > > > > Below are the tables, queries, and execution plans with my questions with > > more detail. (Since I have 250 partitions, I can query one partition > after > > the other to ensure that I'm not pulling results form the cache) > > Doesn't that explain why it is slow? If you have 15000 rpm drives and > each row is in a different block and uncached, it would take 20 > seconds to read them all in. You are getting 10 times better than > that, either due to caching or because your rows are clustered, or > because effective_io_concurrency is doing its thing. > > > > > explain analyze select data_id, dataset_id, stat from data_par
Re: [PERFORM] Expected performance of querying 5k records from 4 million records?
On Mon, Jun 18, 2012 at 9:39 AM, Anish Kejariwal wrote: > > So, it looks like clustering the index appropriately fixes things! Also, > I'll recreate the index switching the order to (dataset_id, stat_id,data_id) > > Just keep in mind that clustering is a one-time operation. Inserts and updates will change the order of records in the table, so you'll need to re-cluster periodically to keep performance high if there are a lot of inserts and updates into the tables. I didn't re-read the thread, but I seem recall a partitioned table, so assuming you are partitioning in a manner which keeps the number of partitions that are actively being inserted/updated on to a minimum, you only need to cluster the active partitions, which isn't usually terribly painful. Also, if you are bulk loading data (and not creating random spaces in the table by deleting and updating), you can potentially order the data on the way into the table to avoid the need to cluster repeatedly. --sam
Re: [PERFORM] Expected performance of querying 5k records from 4 million records?
On 6/18/12 9:39 AM, Anish Kejariwal wrote: > Thanks for the help, Jeff and Josh. It looks reclustering the multi-column > index might solve things. For my particular query, because I'm getting a > range of records back, it makes sense that reclustering will benefit me if > I have a slow disk even if I had expected that the indices would be > sufficient . I now need to make sure that the speed up I'm seeing is not > because things have been cached. Well, other than that your performance is as expected because of your much-larger-than-RAM database and your relatively slow disk. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
