Follow along with the video below to see how to install our site as a web app on your home screen.
Anmerkung: This feature may not be available in some browsers.
column1 | column2 | column3 | column4 |
1 | 2 | 3 | 4 |
2 | 2a | 3b | |
3 | 3a | 3b | |
4 | 4a | 4b | 4c |
5 | 5a | 5b | 5c |
6 | 6a | 6b | |
7 | 7a | 7b | |
8 | 8a | 8b | 8c |
9 | 9a | 9b | 9c |
test=# select * from notablegiven;
column1 | column2 | column3 | column4
---------+---------+---------+---------
1 | 2 | 3 | 4
2 | 2a | 3b |
3 | 3a | 3b |
(3 Zeilen)
test=# select * from notablegiven where column4 is not null and column4 != ' ';
column1 | column2 | column3 | column4
---------+---------+---------+---------
1 | 2 | 3 | 4
(1 Zeile)
Das mag sein. Deine Frage lässt sich nicht beantworten, weil niemand weiß, was sie bedeuten soll!für rows 2&3 wollte ich das ignorieren. so ich habe column4 IS NOT NULL und TRIM benutzt. ist das richtige ?
privat=*# create table bla(t text);
CREATE TABLE
privat=*# insert into bla select 'x' from generate_series(1,10000000) x;
INSERT 0 10000000
privat=*# insert into bla values (' ');
INSERT 0 1
privat=*# explain analyse select * from bla where trim(t) = '';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on bla (cost=0.00..134513.92 rows=30089 width=32) (actual time=3967.888..3967.890 rows=1 loops=1)
Filter: (btrim(t) = ''::text)
Rows Removed by Filter: 10000000
Planning time: 2.424 ms
Execution time: 3968.227 ms
(5 rows)
privat=*# create index idx1 on bla((trim(t)));
CREATE INDEX
privat=*# explain analyse select * from bla where trim(t) = '';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on bla (cost=939.93..48041.70 rows=50000 width=32) (actual time=0.061..0.062 rows=1 loops=1)
Recheck Cond: (btrim(t) = ''::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx1 (cost=0.00..927.43 rows=50000 width=0) (actual time=0.051..0.051 rows=1 loops=1)
Index Cond: (btrim(t) = ''::text)
Planning time: 2.110 ms
Execution time: 0.281 ms
(7 rows)
privat=*# create index idx2 on bla(t) where (trim(t) = '');
CREATE INDEX
privat=*# explain analyse select * from bla where trim(t) = '';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on bla (cost=16.63..47118.40 rows=50000 width=32) (actual time=0.024..0.025 rows=1 loops=1)
Recheck Cond: (btrim(t) = ''::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx2 (cost=0.00..4.13 rows=50000 width=0) (actual time=0.013..0.013 rows=1 loops=1)
Planning time: 0.121 ms
Execution time: 0.059 ms
(6 rows)
Klar, man kann auch alle seine Daten löschen, dann braucht man keinen Index mehr...Wieso sollte man seine Daten bereinigen, wenn man einen (unnötigen) Index anlegen kann?
Dazu müßte man das Problem des Fragestellers kennen & verstehen - dies ist mir nicht gelungen.Bin nicht sicher, glaubeNORMALISIERUNGwar war das Zauberwort.
War das nicht dein Steckenpferd??