pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedView not using index
6+ messages / 3 participants
[nested] [flat]
* View not using index
@ 2005-06-13 06:54 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 6+ messages in thread
From: Yves Vindevogel @ 2005-06-13 06:54 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: View not using index
@ 2005-06-13 07:05 Russell Smith <mr-russ@pws.com.au>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 2 replies; 6+ messages in thread
From: Russell Smith @ 2005-06-13 07:05 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: View not using index
@ 2005-06-13 07:18 Yves Vindevogel <yves.vindevogel@implements.be>
parent: Russell Smith <mr-russ@pws.com.au>
1 sibling, 2 replies; 6+ messages in thread
From: Yves Vindevogel @ 2005-06-13 07:18 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: View not using index
@ 2005-06-13 07:18 Russell Smith <mr-russ@pws.com.au>
parent: Russell Smith <mr-russ@pws.com.au>
1 sibling, 0 replies; 6+ messages in thread
From: Russell Smith @ 2005-06-13 07:18 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: View not using index
@ 2005-06-13 07:35 Yves Vindevogel <yves.vindevogel@implements.be>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 0 replies; 6+ messages in thread
From: Yves Vindevogel @ 2005-06-13 07:35 UTC (permalink / raw)
To: 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:
> 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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: View not using index
@ 2005-06-13 14:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-06-13 14:18 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-06-13 14:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-13 06:54 View not using index Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 07:05 ` Russell Smith <mr-russ@pws.com.au>
2005-06-13 07:18 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 07:35 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-13 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 07:18 ` Russell Smith <mr-russ@pws.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox