pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSeqscan
4+ messages / 3 participants
[nested] [flat]
* Seqscan
@ 2007-10-23 02:24 Adrian Demaestri <ademaestri@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Demaestri @ 2007-10-23 02:24 UTC (permalink / raw)
To: pgsql-performance
Hi,
I think planner should use other plans than seqscan to solve querys like select * from hugetable limit 1, especially when the talbe is very large. Is it solved in newer versions or is there some open issues about it?.
thanks
I'm working with postgres 8.0.1,
---------------------------------
¡Sé un mejor fotógrafo!
Perfecciona tu técnica y encuentra las mejores fotos en:
http://telemundo.yahoo.com/promos/mejorfotografo.html
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Seqscan
@ 2007-10-23 02:45 Jeff Davis <pgsql@j-davis.com>
parent: Adrian Demaestri <ademaestri@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Davis @ 2007-10-23 02:45 UTC (permalink / raw)
To: Adrian Demaestri <ademaestri@yahoo.com>; +Cc: pgsql-performance
On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote:
> Hi,
> I think planner should use other plans than seqscan to solve querys
> like select * from hugetable limit 1, especially when the talbe is
> very large. Is it solved in newer versions or is there some open
> issues about it?.
> thanks
> I'm working with postgres 8.0.1,
For the query in question, what would be faster than a seqscan? It
doesn't read the whole table, it only reads until it satisfies the limit
clause.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Seqscan
@ 2007-10-23 13:06 Adrian Demaestri <ademaestri@yahoo.com>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Demaestri @ 2007-10-23 13:06 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-performance
It is not actualy a table, sorry, it is a quite complex view that involve three large tables. When I query the view using a where clause the answer is fast because of the use of some restrictive indexes, but when there is no where clause the "limit 1" waits until the entire table is generated and all the joins are made. I can't control the sintax of the problematic query, it is generated autamatically by another layer of our application and it's syntactically and semantically ok.
Here is the view structure
SELECT a.field1
FROM a
LEFT JOIN b ON a.f1= b.f1 AND a.f2 = b.f2
LEFT JOIN c ON a.f3 = c.f3
Each one of the tables a, b and c has about 5 million rows.
The relation between the tables using that joins conditions is at most 1 to 1
thanks!
Jeff Davis <pgsql@j-davis.com> escribió: On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote:
> Hi,
> I think planner should use other plans than seqscan to solve querys
> like select * from hugetable limit 1, especially when the talbe is
> very large. Is it solved in newer versions or is there some open
> issues about it?.
> thanks
> I'm working with postgres 8.0.1,
For the query in question, what would be faster than a seqscan? It
doesn't read the whole table, it only reads until it satisfies the limit
clause.
Regards,
Jeff Davis
---------------------------------
¡Sé un mejor ambientalista!
Encuentra consejos para cuidar el lugar donde vivimos en:
http://telemundo.yahoo.com/promos/mejorambientalista.html
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Seqscan
@ 2007-10-23 13:36 Nis Jørgensen <nis@superlativ.dk>
parent: Adrian Demaestri <ademaestri@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Nis Jørgensen @ 2007-10-23 13:36 UTC (permalink / raw)
To: pgsql-performance
(Please don't top-post. )
Adrian Demaestri skrev:
> */Jeff Davis <pgsql@j-davis.com>/* escribió:
>
> On Mon, 2007-10-22 at 19:24 -0700, Adrian Demaestri wrote:
> > Hi,
> > I think planner should use other plans than seqscan to solve querys
> > like select * from hugetable limit 1, especially when the talbe is
> > very large. Is it solved in newer versions or is there some open
> > issues about it?.
> > thanks
> > I'm working with postgres 8.0.1,
>
> For the query in question, what would be faster than a seqscan? It
> doesn't read the whole table, it only reads until it satisfies the limit
> clause.
> It is not actualy a table, sorry, it is a quite complex view that
> involve three large tables.
If hugetable isn't a table, you chose a really bad name for it.
What you have here is a specific query performing badly, not a generic
issue with all queries containing "LIMIT X". You might of course have
found a construct which the planner has problems with - but the first
step is to let us see the result of EXPLAIN ANALYZE.
Anyway, I think you might be hitting this issue:
"Fix mis-planning of queries with small LIMIT values due to poorly
thought out "fuzzy" cost comparison"
(http://www.postgresql.org/docs/8.0/static/release-8-0-4.html)
which was fixed in 8.0.4 . You should upgrade.
Nis
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-10-23 13:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-23 02:24 Seqscan Adrian Demaestri <ademaestri@yahoo.com>
2007-10-23 02:45 ` Jeff Davis <pgsql@j-davis.com>
2007-10-23 13:06 ` Adrian Demaestri <ademaestri@yahoo.com>
2007-10-23 13:36 ` Nis Jørgensen <nis@superlativ.dk>
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