agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedView not using index
4+ messages / 3 participants
[nested] [flat]
* View not using index
@ 2015-09-15 11:56 gmb <gmbouwer@gmail.com>
2015-09-15 13:18 ` Re: View not using index Brice André <brice@famille-andre.be>
2015-09-15 13:29 ` Re: View not using index Igor Neyman <ineyman@perceptron.com>
0 siblings, 2 replies; 4+ messages in thread
From: gmb @ 2015-09-15 11:56 UTC (permalink / raw)
To: pgsql-sql
HI
I hope somebody can give some guidance.
Since our application make extensive use of views, this is becoming a
concern for me.
Please see below:
CREATE TABLE detail ( invno VARCHAR, accno INTEGER, info INTEGER[] );
CREATE OR REPLACE VIEW detailview AS
( SELECT invno , accno , COALESCE( info[1],0 ) info1, COALESCE( info[2],0 )
info2, COALESCE( info[3],0 ) info3, COALESCE( info[4],0 ) info4 FROM detail
);
CREATE INDEX detail_ix_info3 ON detail ( ( info[3] ) ) WHERE COALESCE(
info[3],0 ) = 1;
EXPLAIN SELECT * FROM detail WHERE COALESCE( info[3],0 ) =1;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on detail (cost=4.13..12.59 rows=4 width=68)
Recheck Cond: (COALESCE(info[3], 0) = 1)
-> Bitmap Index Scan on detail_ix_info3 (cost=0.00..4.13 rows=4
width=0)
(3 rows)
EXPLAIN SELECT * FROM detailview WHERE COALESCE( info3,0 ) =1;
QUERY PLAN
--------------------------------------------------------
Seq Scan on detail (cost=0.00..20.38 rows=4 width=68)
Filter: (COALESCE(COALESCE(info[3], 0), 0) = 1)
(2 rows)
This is an oversimplified example; the view in our production env provides
for 20 elements in the info array column. My table in productions env
contains ~10mil rows.
Is there any way in which I can force the view to use the index?
Regards
--
View this message in context: http://postgresql.nabble.com/View-not-using-index-tp5865953.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View not using index
2015-09-15 11:56 View not using index gmb <gmbouwer@gmail.com>
@ 2015-09-15 13:18 ` Brice André <brice@famille-andre.be>
2015-09-16 04:45 ` Re: View not using index gmb <gmbouwer@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Brice André @ 2015-09-15 13:18 UTC (permalink / raw)
To: gmb <gmbouwer@gmail.com>; +Cc: pgsql-sql
Dear Gmb,
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View not using index
2015-09-15 11:56 View not using index gmb <gmbouwer@gmail.com>
2015-09-15 13:18 ` Re: View not using index Brice André <brice@famille-andre.be>
@ 2015-09-16 04:45 ` gmb <gmbouwer@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: gmb @ 2015-09-16 04:45 UTC (permalink / raw)
To: pgsql-sql
Thanks for the reply, Brice.
Brice André wrote
> - If your test DB has so few data that it is not efficient to use the
> index, this last will not be used. You should probably try to insert
> more
> data before performing the test
> - the query planner uses table statistics to decide if it uses an
> index.
> But, if those statistics are not up-to-date, the choice can be not
> optimal.
> You should maybe try to look at 'Analyse' command to get more info :
As I said, the data in my production env is populated but I'm getting the
same results, even after running ANALYZE on the tables.
--
View this message in context: http://postgresql.nabble.com/View-not-using-index-tp5865953p5866097.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View not using index
2015-09-15 11:56 View not using index gmb <gmbouwer@gmail.com>
@ 2015-09-15 13:29 ` Igor Neyman <ineyman@perceptron.com>
1 sibling, 0 replies; 4+ messages in thread
From: Igor Neyman @ 2015-09-15 13:29 UTC (permalink / raw)
To: gmb <gmbouwer@gmail.com>; pgsql-sql
CREATE TABLE detail ( invno VARCHAR, accno INTEGER, info INTEGER[] );
CREATE OR REPLACE VIEW detailview AS
( SELECT invno , accno , COALESCE( info[1],0 ) info1, COALESCE( info[2],0 ) info2, COALESCE( info[3],0 ) info3, COALESCE( info[4],0 ) info4 FROM detail );
CREATE INDEX detail_ix_info3 ON detail ( ( info[3] ) ) WHERE COALESCE(
info[3],0 ) = 1;
EXPLAIN SELECT * FROM detail WHERE COALESCE( info[3],0 ) =1;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on detail (cost=4.13..12.59 rows=4 width=68)
Recheck Cond: (COALESCE(info[3], 0) = 1)
-> Bitmap Index Scan on detail_ix_info3 (cost=0.00..4.13 rows=4
width=0)
(3 rows)
EXPLAIN SELECT * FROM detailview WHERE COALESCE( info3,0 ) =1;
QUERY PLAN
--------------------------------------------------------
Seq Scan on detail (cost=0.00..20.38 rows=4 width=68)
Filter: (COALESCE(COALESCE(info[3], 0), 0) = 1)
(2 rows)
This is an oversimplified example; the view in our production env provides for 20 elements in the info array column. My table in productions env contains ~10mil rows.
Is there any way in which I can force the view to use the index?
_______________________
Why are you applying "extra" COALESCE when querying the view?
Why not just:
SELECT * FROM detailview WHERE infor3 = 1;
?
Regards,
Igor Neyman
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-09-16 04:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-09-15 11:56 View not using index gmb <gmbouwer@gmail.com>
2015-09-15 13:18 ` Brice André <brice@famille-andre.be>
2015-09-16 04:45 ` gmb <gmbouwer@gmail.com>
2015-09-15 13:29 ` Igor Neyman <ineyman@perceptron.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox