Thread: View not using index
Hi, I have a view that has something like this: select x, y, z from tbl order by x, y I have created a special index on x + y I have run analyze Still, when I use explain, pg says it will first sort my tables instead of using my index How is that possible ? When I do explain select x,y,z from tbl order by x, y, it works like I want it to work Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>Hi, I have a view that has something like this: select x, y, z from tbl order by x, y I have created a special index on x + y I have run analyze Still, when I use explain, pg says it will first sort my tables instead of using my index How is that possible ? When I do explain select x,y,z from tbl order by x, y, it works like I want it to work 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.
Attachment
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
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>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
<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.
Attachment
Please CC the list. On Mon, 13 Jun 2005 05:11 pm, Yves Vindevogel wrote: > create or replace view vw_document_pagesperjob as > select documentname, eventdate, eventtime, loginuser, > fnFormatInt(pages) as pages > from tblPrintjobs > order by descpages, documentname ; > > 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) > Postgresql must scan the entire heap anyway, so ordering in memory will be faster, and you don't have to load the pages from disk in a random order. > 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) > That's because an index scan is only useful if you are scanning a small percentage of the table. Which you are doing when you have the limit clause. > Strange thing is, when I immediately add the limit clause, it runs like > I want it to run. I am not sure of the usefulness of the first query anyway, it returns a lot of data. How do you expect it not to scan the whole table when you want all the data form the table? > Problem is that I run this from Cocoon. Cocoon adds the limit clause > itself. > Maybe I need to rewrite everything in functions instead of views. > Functions, views. It will make not difference. The issue is the amount of data returned relative to the amount of data in the table. Regards Russell Smith
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.
Attachment
Yves Vindevogel <yves.vindevogel@implements.be> writes:
> 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)
In general, putting an ORDER BY inside a view isn't a great idea ---
it's not legal per SQL spec (hence not portable), and it defeats most
forms of optimization of the view.
CVS tip is actually able to do what you wish with the above case, but no
existing release will optimize the view's ORDER BY in light of a LIMIT
that's outside the view.
regards, tom lane