All geek questions in one place

Incorrect index scanning in Postgres in multiple field conditions?

I have several large tables in Postgres 9.2 (millions of rows) where I need to create a unique code based on a combination of two fields: "source" (varchar) and "id" (int). I can do this by creating row_numbers as a result:

SELECT source,id FROM tablename GROUP BY source,id

but the results may take some time. It was recommended that if the fields were indexed and there is a proportionally small number of index values ​​(which is my case), then a good index check might be the best option: http://wiki.postgresql.org/wiki/Loose_indexscan

 WITH RECURSIVE t AS (SELECT min(col) AS col FROM tablename UNION ALL SELECT (SELECT min(col) FROM tablename WHERE col > t.col) FROM t WHERE t.col IS NOT NULL) SELECT col FROM t WHERE col IS NOT NULL UNION ALL SELECT NULL WHERE EXISTS(SELECT * FROM tablename WHERE col IS NULL);

The example works in only one field. Trying to return more than one field generates an error: only one column should be returned in a subquery. One possibility might be to try to get the whole ROW — for example, SELECT ROW(min(source),min(id)... , but then I'm not sure if the syntax of the WHERE statement should look like working with individual elements of a row.

Question: Is it possible to change the code based on recursion to work with several columns, and if so, how ? I intend to use Postgres, but it looks like MySQL has implemented simple index scans for more than one column: http://dev.mysql.com/doc/refman/5.1/en/group-by-optimization.html

As recommended, I am enclosing the results of EXPLAIN ANALYZE.

In my situation - where I select different values ​​for 2 columns using GROUP BY , this is the following:

  HashAggregate (cost=1645408.44..1654099.65 rows=869121 width=34) (actual time=35411.889..36008.475 rows=1233080 loops=1) -> Seq Scan on tablename (cost=0.00..1535284.96 rows=22024696 width=34) (actual time=4413.311..25450.840 rows=22025768 loops=1) Total runtime: 36127.789 ms (3 rows)

I don't know how to perform an index scan with two columns (this is a question), but for comparison purposes , using GROUP BY in one column, I get:

  HashAggregate (cost=1590346.70..1590347.69 rows=99 width=8) (actual time=32310.706..32310.722 rows=100 loops=1) -> Seq Scan on tablename (cost=0.00..1535284.96 rows=22024696 width=8) (actual time=4764.609..26941.832 rows=22025768 loops=1) Total runtime: 32350.899 ms (3 rows)

But for a quick index scan in one column, I get:

  Result (cost=181.28..198.07 rows=101 width=8) (actual time=0.069..1.935 rows=100 loops=1) CTE t -> Recursive Union (cost=1.74..181.28 rows=101 width=8) (actual time=0.062..1.855 rows=101 loops=1) -> Result (cost=1.74..1.75 rows=1 width=0) (actual time=0.061..0.061 rows=1 loops=1) InitPlan 1 (returns $1) -> Limit (cost=0.00..1.74 rows=1 width=8) (actual time=0.057..0.057 rows=1 loops=1) -> Index Only Scan using tablename_id on tablename (cost=0.00..38379014.12 rows=22024696 width=8) (actual time=0.055..0.055 rows=1 loops=1) Index Cond: (id IS NOT NULL) Heap Fetches: 0 -> WorkTable Scan on t (cost=0.00..17.75 rows=10 width=8) (actual time=0.017..0.017 rows=1 loops=101) Filter: (id IS NOT NULL) Rows Removed by Filter: 0 SubPlan 3 -> Result (cost=1.75..1.76 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=100) InitPlan 2 (returns $3) -> Limit (cost=0.00..1.75 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=100) -> Index Only Scan using tablename_id on tablename (cost=0.00..12811462.41 rows=7341565 width=8) (actual time=0.015..0.015 rows=1 loops=100) Index Cond: ((id IS NOT NULL) AND (id > t.id)) Heap Fetches: 0 -> Append (cost=0.00..16.79 rows=101 width=8) (actual time=0.067..1.918 rows=100 loops=1) -> CTE Scan on t (cost=0.00..2.02 rows=100 width=8) (actual time=0.067..1.899 rows=100 loops=1) Filter: (id IS NOT NULL) Rows Removed by Filter: 1 -> Result (cost=13.75..13.76 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1) One-Time Filter: $5 InitPlan 5 (returns $5) -> Index Only Scan using tablename_id on tablename (cost=0.00..13.75 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1) Index Cond: (id IS NULL) Heap Fetches: 0 Total runtime: 2.040 ms

A full table definition is as follows:

 CREATE TABLE tablename ( source character(25), id bigint NOT NULL, time_ timestamp without time zone, height numeric, lon numeric, lat numeric, distance numeric, status character(3), geom geometry(PointZ,4326), relid bigint ) WITH ( OIDS=FALSE ); CREATE INDEX tablename_height ON public.tablename USING btree (height); CREATE INDEX tablename_geom ON public.tablename USING gist (geom); CREATE INDEX tablename_id ON public.tablename USING btree (id); CREATE INDEX tablename_lat ON public.tablename USING btree (lat); CREATE INDEX tablename_lon ON public.tablename USING btree (lon); CREATE INDEX tablename_relid ON public.tablename USING btree (relid); CREATE INDEX tablename_sid ON public.tablename USING btree (source COLLATE pg_catalog."default", id); CREATE INDEX tablename_source ON public.tablename USING btree (source COLLATE pg_catalog."default"); CREATE INDEX tablename_time ON public.tablename USING btree (time_);

Answer selection:

I spent some time comparing the approaches that were provided. At such moments, I would like more than one answer to be accepted, but in this case I give a checkmark to @jjanes. The reason for this is that its solution corresponds to the task, as originally set more closely, and I was able to get some idea of ​​the form of the required WHERE operator. In the end, HashAggregate is actually the fastest approach (for me), but due to the nature of my data, there are no problems with the algorithms. I have attached EXPLAIN ANALYZE for the different approaches below and give +1 for jjanes and joop.

HashAggregate:

  HashAggregate (cost=1018669.72..1029722.08 rows=1105236 width=34) (actual time=24164.735..24686.394 rows=1233080 loops=1) -> Seq Scan on tablename (cost=0.00..908548.48 rows=22024248 width=34) (actual time=0.054..14639.931 rows=22024982 loops=1) Total runtime: 24787.292 ms

Incorrect index scan modification

 CTE Scan on t (cost=13.84..15.86 rows=100 width=112) (actual time=0.916..250311.164 rows=1233080 loops=1) Filter: (source IS NOT NULL) Rows Removed by Filter: 1 CTE t -> Recursive Union (cost=0.00..13.84 rows=101 width=112) (actual time=0.911..249295.872 rows=1233081 loops=1) -> Limit (cost=0.00..0.04 rows=1 width=34) (actual time=0.910..0.911 rows=1 loops=1) -> Index Only Scan using tablename_sid on tablename (cost=0.00..965442.32 rows=22024248 width=34) (actual time=0.908..0.908 rows=1 loops=1) Heap Fetches: 0 -> WorkTable Scan on t (cost=0.00..1.18 rows=10 width=112) (actual time=0.201..0.201 rows=1 loops=1233081) Filter: (source IS NOT NULL) Rows Removed by Filter: 0 SubPlan 1 -> Limit (cost=0.00..0.05 rows=1 width=34) (actual time=0.100..0.100 rows=1 loops=1233080) -> Index Only Scan using tablename_sid on tablename (cost=0.00..340173.38 rows=7341416 width=34) (actual time=0.100..0.100 rows=1 loops=1233080) Index Cond: (ROW(source, id) > ROW(t.source, t.id)) Heap Fetches: 0 SubPlan 2 -> Limit (cost=0.00..0.05 rows=1 width=34) (actual time=0.099..0.099 rows=1 loops=1233080) -> Index Only Scan using tablename_sid on tablename (cost=0.00..340173.38 rows=7341416 width=34) (actual time=0.098..0.098 rows=1 loops=1233080) Index Cond: (ROW(source, id) > ROW(t.source, t.id)) Heap Fetches: 0 Total runtime: 250491.559 ms

Antivirus Connections

 Merge Anti Join (cost=0.00..12099015.26 rows=14682832 width=42) (actual time=48.710..541624.677 rows=1233080 loops=1) Merge Cond: ((src.source = nx.source) AND (src.id = nx.id)) Join Filter: (nx.time_ > src.time_) Rows Removed by Join Filter: 363464177 -> Index Only Scan using tablename_pkey on tablename src (cost=0.00..1060195.27 rows=22024248 width=42) (actual time=48.566..5064.551 rows=22024982 loops=1) Heap Fetches: 0 -> Materialize (cost=0.00..1115255.89 rows=22024248 width=42) (actual time=0.011..40551.997 rows=363464177 loops=1) -> Index Only Scan using tablename_pkey on tablename nx (cost=0.00..1060195.27 rows=22024248 width=42) (actual time=0.008..8258.890 rows=22024982 loops=1) Heap Fetches: 0 Total runtime: 541750.026 ms
+6
source share
2 answers

Rather disgusting, but it seems to work:

 WITH RECURSIVE t AS ( select a,b from (select a,b from foo order by a,b limit 1) asdf union all select (select a from foo where (a,b) > (ta,tb) order by a,b limit 1), (select b from foo where (a,b) > (ta,tb) order by a,b limit 1) from t where ta is not null) select * from t where ta is not null;

I really don’t understand why “not zeros” are needed, how and where do zeros come from?

+2
source 
 DROP SCHEMA zooi CASCADE; CREATE SCHEMA zooi ; SET search_path=zooi,public,pg_catalog; CREATE TABLE tablename ( source character(25) NOT NULL , id bigint NOT NULL , time_ timestamp without time zone NOT NULL , height numeric , lon numeric , lat numeric , distance numeric , status character(3) , geom geometry(PointZ,4326) , relid bigint , PRIMARY KEY (source,id,time_) -- <<-- Primary key here ) WITH ( OIDS=FALSE); -- invent some bogus data INSERT INTO tablename(source,id,time_) SELECT 'SRC_'|| (gs%10)::text ,gs/10 ,gt FROM generate_series(1,1000) gs , generate_series('2013-12-01', '2013-12-07', '1hour'::interval) gt ;

Select unique values ​​for two key fields:

 VACUUM ANALYZE tablename; EXPLAIN ANALYZE SELECT source,id,time_ FROM tablename src WHERE NOT EXISTS ( SELECT * FROM tablename nx WHERE nx.source =src.source AND nx.id = src.id AND time_ > src.time_ ) ;

Creates this plan here (Pg-9.3):

  QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Hash Anti Join (cost=4981.00..12837.82 rows=96667 width=42) (actual time=547.218..1194.335 rows=1000 loops=1) Hash Cond: ((src.source = nx.source) AND (src.id = nx.id)) Join Filter: (nx.time_ > src.time_) Rows Removed by Join Filter: 145000 -> Seq Scan on tablename src (cost=0.00..2806.00 rows=145000 width=42) (actual time=0.010..210.810 rows=145000 loops=1) -> Hash (cost=2806.00..2806.00 rows=145000 width=42) (actual time=546.497..546.497 rows=145000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 9063kB -> Seq Scan on tablename nx (cost=0.00..2806.00 rows=145000 width=42) (actual time=0.006..259.864 rows=145000 loops=1) Total runtime: 1197.374 ms (9 rows)

Hash connections are likely to disappear as soon as the data outgrows work_mem:

  Merge Anti Join (cost=0.83..8779.56 rows=29832 width=120) (actual time=0.981..2508.912 rows=1000 loops=1) Merge Cond: ((src.source = nx.source) AND (src.id = nx.id)) Join Filter: (nx.time_ > src.time_) Rows Removed by Join Filter: 184051 -> Index Scan using tablename_sid on tablename src (cost=0.41..4061.57 rows=32544 width=120) (actual time=0.055..250.621 rows=145000 loops=1) -> Index Scan using tablename_sid on tablename nx (cost=0.41..4061.57 rows=32544 width=120) (actual time=0.008..603.403 rows=328906 loops=1) Total runtime: 2510.505 ms
+1
source

Source: https://habr.com/ru/post/959363/

More articles:


All Articles