Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE response (
    session_id integer NOT NULL,
    seconds integer NOT NULL,
    question_id integer NOT NULL,
    response character varying(500),
    file bytea
);

CREATE TABLE


INSERT INTO response(session_id, seconds, question_id, response, file)
VALUES (758,1459505869,31,'0',' '),	(758,1459505869,32,'0',' '),	
(758,1459505869,33,'0',' '),	(758,1459505869,34,'0',' '),	
(758,1459505869,35,'1',' '),	(758,1459505869,36,'0',' '),	
(758,1459505869,37,'0',' '),	(758,1459505869,38,'0',' '),	
(758,1459506973,38,'0',' '),	(758,1459506973,37,'0',' '),	
(758,1459506973,36,'0',' '),(758,1459506973,35,'1',' '),	
(758,1459506973,34,'0',' '),(758,1459506973,33,'0',' '),	
(758,1459506973,32,'0',' '),(758,1459506973,31,'0',' '),
(758,1459508676,31,'0',' '),(758,1459508676,32,'0',' '),	
(758,1459508676,33,'0',' '),(758,1459508676,34,'0',' '),	
(758,1459508676,35,'1',' '),(758,1459508676,36,'0',' '),	
(758,1459508676,37,'0', ' '),	(758,1459508676,38,'0', ' ');

INSERT 0 24


SELECT * FROM response


session_id
seconds
question_id
response
file


758
1459505869
31
0
\x20

758
1459505869
32
0
\x20

758
1459505869
33
0
\x20

758
1459505869
34
0
\x20

758
1459505869
35
1
\x20

758
1459505869
36
0
\x20

758
1459505869
37
0
\x20

758
1459505869
38
0
\x20

758
1459506973
38
0
\x20

758
1459506973
37
0
\x20

758
1459506973
36
0
\x20

758
1459506973
35
1
\x20

758
1459506973
34
0
\x20

758
1459506973
33
0
\x20

758
1459506973
32
0
\x20

758
1459506973
31
0
\x20

758
1459508676
31
0
\x20

758
1459508676
32
0
\x20

758
1459508676
33
0
\x20

758
1459508676
34
0
\x20

758
1459508676
35
1
\x20

758
1459508676
36
0
\x20

758
1459508676
37
0
\x20

758
1459508676
38
0
\x20



SELECT 24


SELECT aresult.session_id,
   aresult.not_foot_count,
    aresult.not_moving,
    aresult.foot,
    aresult.bike,
    aresult.motor,
    aresult.car,
    aresult.bus,
    aresult.metro,
    aresult.train,
    aresult.other
   FROM crosstab('select session_id, question_id, response 
                from  response WHERE session_id=758
                order by session_id,question_id'::text) 
  aresult(session_id integer, not_foot_count character varying(500), 
  not_moving character varying(500), foot character varying(500), 
  bike character varying(500), motor character varying(500), 
  car character varying(500), bus character varying(500), 
  metro character varying(500), train character varying(500), 
  other character varying(500))


session_id
not_foot_count
not_moving
foot
bike
motor
car
bus
metro
train
other


758
0
0
0
0
0
0
0
0
0
0



SELECT 1


select * from response limit 5


session_id
seconds
question_id
response
file


758
1459505869
31
0
\x20

758
1459505869
32
0
\x20

758
1459505869
33
0
\x20

758
1459505869
34
0
\x20

758
1459505869
35
1
\x20



SELECT 5

session_id	seconds	question_id	response	file
758	1459505869	31	0	\x20
758	1459505869	32	0	\x20
758	1459505869	33	0	\x20
758	1459505869	34	0	\x20
758	1459505869	35	1	\x20
758	1459505869	36	0	\x20
758	1459505869	37	0	\x20
758	1459505869	38	0	\x20
758	1459506973	38	0	\x20
758	1459506973	37	0	\x20
758	1459506973	36	0	\x20
758	1459506973	35	1	\x20
758	1459506973	34	0	\x20
758	1459506973	33	0	\x20
758	1459506973	32	0	\x20
758	1459506973	31	0	\x20
758	1459508676	31	0	\x20
758	1459508676	32	0	\x20
758	1459508676	33	0	\x20
758	1459508676	34	0	\x20
758	1459508676	35	1	\x20
758	1459508676	36	0	\x20
758	1459508676	37	0	\x20
758	1459508676	38	0	\x20