Re: View not using index
От | Yves Vindevogel |
---|---|
Тема | Re: View not using index |
Дата | |
Msg-id | d0e84110335f1e6d7e4c9b0c0b2b2266@implements.be обсуждение исходный текст |
Ответ на | Re: View not using index (Yves Vindevogel <yves.vindevogel@implements.be>) |
Список | pgsql-performance |
Note the last query below (prev post) There it does use the index rvponp=# create type tpJobsPerDay as rvponp-# ( documentname varchar(1000), rvponp(# eventdate date, rvponp(# eventtime time, rvponp(# loginuser varchar(255), rvponp(# pages varchar(20) rvponp(# ) ; CREATE TYPE rvponp=# create function fnJobsPerDay (bigint, bigint) returns setof tpJobsPerDay as rvponp-# ' rvponp'# select documentname, eventdate, eventtime, loginuser, fnFormatInt(pages) as pages rvponp'# from tblPrintjobs order by descpages, documentname rvponp'# offset $1 limit $2 ; rvponp'# ' language 'sql' ; CREATE FUNCTION rvponp=# analyze ; ANALYZE rvponp=# explain select * from fnJobsperday (1, 10) ; QUERY PLAN ----------------------------------------------------------------------- Function Scan on fnjobsperday (cost=0.00..12.50 rows=1000 width=697) (1 row) With the function, it still is very slow. I can't see anything in the explain here, but it seems to be using a table scan. On 13 Jun 2005, at 09:18, Yves Vindevogel wrote: <excerpt>rvponp=# explain select * from vw_document_pagesperjob ; QUERY PLAN ---------------------------------------------------------------------------------------- Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 rows=588209 width=706) -> Sort (cost=82796.59..84267.11 rows=588209 width=74) Sort Key: tblprintjobs.descpages, tblprintjobs.documentname -> Seq Scan on tblprintjobs (cost=0.00..26428.61 rows=588209 width=74) (4 rows) rvponp=# explain select * from vw_document_pagesperjob limit 10 ; QUERY PLAN ---------------------------------------------------------------------------------------------- Limit (cost=82796.59..82796.72 rows=10 width=706) -> Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 rows=588209 width=706) -> Sort (cost=82796.59..84267.11 rows=588209 width=74) Sort Key: tblprintjobs.descpages, tblprintjobs.documentname -> Seq Scan on tblprintjobs (cost=0.00..26428.61 rows=588209 width=74) (5 rows) rvponp=# explain select documentname, eventdate, eventtime, loginuser, pages from tblPrintjobs order by descpages, documentname ; QUERY PLAN ---------------------------------------------------------------------------- Sort (cost=81326.07..82796.59 rows=588209 width=74) Sort Key: descpages, documentname -> Seq Scan on tblprintjobs (cost=0.00..24958.09 rows=588209 width=74) (3 rows) rvponp=# explain select documentname, eventdate, eventtime, loginuser, pages from tblPrintjobs order by descpages, documentname limit 10 ; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..33.14 rows=10 width=74) -> Index Scan using ixprintjobspagesperjob on tblprintjobs (cost=0.00..1949116.68 rows=588209 width=74) (2 rows) <fixed><x-tad-bigger>create or replace view vw_document_pagesperjob as select documentname, eventdate, eventtime, loginuser, fnFormatInt(pages) as pages from tblPrintjobs order by descpages, documentname ; </x-tad-bigger></fixed> On 13 Jun 2005, at 09:05, Russell Smith wrote: <excerpt>On Mon, 13 Jun 2005 04:54 pm, Yves Vindevogel wrote: <excerpt>Still, when I use explain, pg says it will first sort my tables instead of using my index How is that possible ? </excerpt> Can we see the output of the explain analyze? The definition of the view? Regards Russell Smith </excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller><<Pasted Graphic 2.tiff><smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller></excerpt><excerpt> ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq </excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>Note the last query below (prev post) There it does use the index rvponp=# create type tpJobsPerDay as rvponp-# ( documentname varchar(1000), rvponp(# eventdate date, rvponp(# eventtime time, rvponp(# loginuser varchar(255), rvponp(# pages varchar(20) rvponp(# ) ; CREATE TYPE rvponp=# create function fnJobsPerDay (bigint, bigint) returns setof tpJobsPerDay as rvponp-# ' rvponp'# select documentname, eventdate, eventtime, loginuser, fnFormatInt(pages) as pages rvponp'# from tblPrintjobs order by descpages, documentname rvponp'# offset $1 limit $2 ; rvponp'# ' language 'sql' ; CREATE FUNCTION rvponp=# analyze ; ANALYZE rvponp=# explain select * from fnJobsperday (1, 10) ; QUERY PLAN ----------------------------------------------------------------------- Function Scan on fnjobsperday (cost=0.00..12.50 rows=1000 width=697) (1 row) With the function, it still is very slow. I can't see anything in the explain here, but it seems to be using a table scan. On 13 Jun 2005, at 09:18, Yves Vindevogel wrote: > rvponp=# explain select * from vw_document_pagesperjob ; > QUERY PLAN > ----------------------------------------------------------------------- > ----------------- > Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 > rows=588209 width=706) > -> Sort (cost=82796.59..84267.11 rows=588209 width=74) > Sort Key: tblprintjobs.descpages, tblprintjobs.documentname > -> Seq Scan on tblprintjobs (cost=0.00..26428.61 > rows=588209 width=74) > (4 rows) > > rvponp=# explain select * from vw_document_pagesperjob limit 10 ; > QUERY PLAN > ----------------------------------------------------------------------- > ----------------------- > Limit (cost=82796.59..82796.72 rows=10 width=706) > -> Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20 > rows=588209 width=706) > -> Sort (cost=82796.59..84267.11 rows=588209 width=74) > Sort Key: tblprintjobs.descpages, > tblprintjobs.documentname > -> Seq Scan on tblprintjobs (cost=0.00..26428.61 > rows=588209 width=74) > (5 rows) > > rvponp=# explain select documentname, eventdate, eventtime, loginuser, > pages from tblPrintjobs order > by descpages, documentname ; > QUERY PLAN > ----------------------------------------------------------------------- > ----- > Sort (cost=81326.07..82796.59 rows=588209 width=74) > Sort Key: descpages, documentname > -> Seq Scan on tblprintjobs (cost=0.00..24958.09 rows=588209 > width=74) > (3 rows) > > rvponp=# explain select documentname, eventdate, eventtime, loginuser, > pages from tblPrintjobs order > by descpages, documentname limit 10 ; > QUERY PLAN > ----------------------------------------------------------------------- > -------------------------------------- > Limit (cost=0.00..33.14 rows=10 width=74) > -> Index Scan using ixprintjobspagesperjob on tblprintjobs > (cost=0.00..1949116.68 rows=588209 width=74) > (2 rows) > > > create or replace view vw_document_pagesperjob as > select documentname, eventdate, eventtime, loginuser, > fnFormatInt(pages) as pages > from tblPrintjobs > order by descpages, documentname ; > > > > > > > On 13 Jun 2005, at 09:05, Russell Smith wrote: > >> On Mon, 13 Jun 2005 04:54 pm, Yves Vindevogel wrote: >>> Still, when I use explain, pg says it will first sort my tables >>> instead >>> of using my index >>> How is that possible ? >> >> Can we see the output of the explain analyze? >> The definition of the view? >> >> Regards >> >> Russell Smith >> >> > Met vriendelijke groeten, > Bien à vous, > Kind regards, > > Yves Vindevogel > Implements > > <Pasted Graphic 2.tiff> > > Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 > > Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 > > Web: http://www.implements.be > > First they ignore you. Then they laugh at you. Then they fight you. > Then you win. > Mahatma Ghandi. > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.
Вложения
В списке pgsql-performance по дате отправления: