Thread: Query Time
I'm running the following query:
SELECT * FROM someTable LIMIT 20
I turned on explain analyze (tip #8!) and was told that the query plan was
sequential (no surprise there) with an estimated cost of between 0.00 and
1.07. Surprisingly, the actual time was around 90000 msec (or one-and-a-half
minutes). What's going on?
On Wed, 13 Aug 2003, Lufkin, Brad wrote: > I'm running the following query: > > SELECT * FROM someTable LIMIT 20 > > I turned on explain analyze (tip #8!) and was told that the query plan was > sequential (no surprise there) with an estimated cost of between 0.00 and > 1.07. Surprisingly, the actual time was around 90000 msec (or one-and-a-half > minutes). What's going on? costs are estimated as a percentage of a cost of a single page access in sequential mode. I.e. a single sequential page access is assumed to cost 1.0, and everything is relative to that. The cost numbers do NOT translate directly into any time unit.
Thanks for the info. Still, I'm surprised that a limited query would take 90 seconds. -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Wednesday, August 13, 2003 4:20 PM To: Lufkin, Brad Cc: 'psql' Subject: Re: [JDBC] Query Time On Wed, 13 Aug 2003, Lufkin, Brad wrote: > I'm running the following query: > > SELECT * FROM someTable LIMIT 20 > > I turned on explain analyze (tip #8!) and was told that the query plan was > sequential (no surprise there) with an estimated cost of between 0.00 and > 1.07. Surprisingly, the actual time was around 90000 msec (or one-and-a-half > minutes). What's going on? costs are estimated as a percentage of a cost of a single page access in sequential mode. I.e. a single sequential page access is assumed to cost 1.0, and everything is relative to that. The cost numbers do NOT translate directly into any time unit.
"Lufkin, Brad" <brad.lufkin@ngc.com> writes:
> I'm running the following query:
> SELECT * FROM someTable LIMIT 20
> I turned on explain analyze (tip #8!) and was told that the query plan was
> sequential (no surprise there) with an estimated cost of between 0.00 and
> 1.07. Surprisingly, the actual time was around 90000 msec (or one-and-a-half
> minutes). What's going on?
Lots and lots and lots of dead tuples, perhaps? How often do you vacuum
this table? What does VACUUM VERBOSE have to say about it?
regards, tom lane
How many rows in that table? 90 seconds is a long time for a select to take, period, with or without a limit. On Wed, 13 Aug 2003, Lufkin, Brad wrote: > Thanks for the info. Still, I'm surprised that a limited query would take 90 > seconds. > > -----Original Message----- > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > Sent: Wednesday, August 13, 2003 4:20 PM > To: Lufkin, Brad > Cc: 'psql' > Subject: Re: [JDBC] Query Time > > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > I'm running the following query: > > > > SELECT * FROM someTable LIMIT 20 > > > > I turned on explain analyze (tip #8!) and was told that the query plan was > > sequential (no surprise there) with an estimated cost of between 0.00 and > > 1.07. Surprisingly, the actual time was around 90000 msec (or > one-and-a-half > > minutes). What's going on? > > costs are estimated as a percentage of a cost of a single page access in > sequential mode. I.e. a single sequential page access is assumed to cost > 1.0, and everything is relative to that. The cost numbers do NOT > translate directly into any time unit. > >
30 million rows....
-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Wednesday, August 13, 2003 4:39 PM
To: Lufkin, Brad
Cc: 'psql'
Subject: Re: [JDBC] Query Time
How many rows in that table? 90 seconds is a long time for a select to
take, period, with or without a limit.
On Wed, 13 Aug 2003, Lufkin, Brad wrote:
> Thanks for the info. Still, I'm surprised that a limited query would take
90
> seconds.
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Wednesday, August 13, 2003 4:20 PM
> To: Lufkin, Brad
> Cc: 'psql'
> Subject: Re: [JDBC] Query Time
>
>
> On Wed, 13 Aug 2003, Lufkin, Brad wrote:
>
> > I'm running the following query:
> >
> > SELECT * FROM someTable LIMIT 20
> >
> > I turned on explain analyze (tip #8!) and was told that the query plan
was
> > sequential (no surprise there) with an estimated cost of between 0.00
and
> > 1.07. Surprisingly, the actual time was around 90000 msec (or
> one-and-a-half
> > minutes). What's going on?
>
> costs are estimated as a percentage of a cost of a single page access in
> sequential mode. I.e. a single sequential page access is assumed to cost
> 1.0, and everything is relative to that. The cost numbers do NOT
> translate directly into any time unit.
>
>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
I'd aree with Tom here that it sounds like there's lots of dead tuples in that table. On Wed, 13 Aug 2003, Lufkin, Brad wrote: > 30 million rows.... > > -----Original Message----- > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > Sent: Wednesday, August 13, 2003 4:39 PM > To: Lufkin, Brad > Cc: 'psql' > Subject: Re: [JDBC] Query Time > > > How many rows in that table? 90 seconds is a long time for a select to > take, period, with or without a limit. > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > Thanks for the info. Still, I'm surprised that a limited query would take > 90 > > seconds. > > > > -----Original Message----- > > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > > Sent: Wednesday, August 13, 2003 4:20 PM > > To: Lufkin, Brad > > Cc: 'psql' > > Subject: Re: [JDBC] Query Time > > > > > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > > > I'm running the following query: > > > > > > SELECT * FROM someTable LIMIT 20 > > > > > > I turned on explain analyze (tip #8!) and was told that the query plan > was > > > sequential (no surprise there) with an estimated cost of between 0.00 > and > > > 1.07. Surprisingly, the actual time was around 90000 msec (or > > one-and-a-half > > > minutes). What's going on? > > > > costs are estimated as a percentage of a cost of a single page access in > > sequential mode. I.e. a single sequential page access is assumed to cost > > 1.0, and everything is relative to that. The cost numbers do NOT > > translate directly into any time unit. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Irrelevant of the number of rows, PG should read 8 tuples and return. There may be alot of deleted tupples at the head of that table. When was the last time you vaccumed? "Lufkin, Brad" wrote: > > 30 million rows.... > > -----Original Message----- > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > Sent: Wednesday, August 13, 2003 4:39 PM > To: Lufkin, Brad > Cc: 'psql' > Subject: Re: [JDBC] Query Time > > How many rows in that table? 90 seconds is a long time for a select to > take, period, with or without a limit. > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > Thanks for the info. Still, I'm surprised that a limited query would take > 90 > > seconds. > > > > -----Original Message----- > > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > > Sent: Wednesday, August 13, 2003 4:20 PM > > To: Lufkin, Brad > > Cc: 'psql' > > Subject: Re: [JDBC] Query Time > > > > > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > > > I'm running the following query: > > > > > > SELECT * FROM someTable LIMIT 20 > > > > > > I turned on explain analyze (tip #8!) and was told that the query plan > was > > > sequential (no surprise there) with an estimated cost of between 0.00 > and > > > 1.07. Surprisingly, the actual time was around 90000 msec (or > > one-and-a-half > > > minutes). What's going on? > > > > costs are estimated as a percentage of a cost of a single page access in > > sequential mode. I.e. a single sequential page access is assumed to cost > > 1.0, and everything is relative to that. The cost numbers do NOT > > translate directly into any time unit. > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
Is there some objective way to confirm this? (Sorry for what may seem like obvious questions, but I'm a newbie with psql--less than one week!) -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Wednesday, August 13, 2003 5:03 PM To: Lufkin, Brad Cc: 'psql' Subject: Re: [JDBC] Query Time I'd aree with Tom here that it sounds like there's lots of dead tuples in that table. On Wed, 13 Aug 2003, Lufkin, Brad wrote: > 30 million rows.... > > -----Original Message----- > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > Sent: Wednesday, August 13, 2003 4:39 PM > To: Lufkin, Brad > Cc: 'psql' > Subject: Re: [JDBC] Query Time > > > How many rows in that table? 90 seconds is a long time for a select to > take, period, with or without a limit. > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > Thanks for the info. Still, I'm surprised that a limited query would take > 90 > > seconds. > > > > -----Original Message----- > > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > > Sent: Wednesday, August 13, 2003 4:20 PM > > To: Lufkin, Brad > > Cc: 'psql' > > Subject: Re: [JDBC] Query Time > > > > > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > > > I'm running the following query: > > > > > > SELECT * FROM someTable LIMIT 20 > > > > > > I turned on explain analyze (tip #8!) and was told that the query plan > was > > > sequential (no surprise there) with an estimated cost of between 0.00 > and > > > 1.07. Surprisingly, the actual time was around 90000 msec (or > > one-and-a-half > > > minutes). What's going on? > > > > costs are estimated as a percentage of a cost of a single page access in > > sequential mode. I.e. a single sequential page access is assumed to cost > > 1.0, and everything is relative to that. The cost numbers do NOT > > translate directly into any time unit. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
vacuum verbose; should tell you something. On Wed, 13 Aug 2003, Lufkin, Brad wrote: > Is there some objective way to confirm this? (Sorry for what may seem like > obvious questions, but I'm a newbie with psql--less than one week!) > > -----Original Message----- > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > Sent: Wednesday, August 13, 2003 5:03 PM > To: Lufkin, Brad > Cc: 'psql' > Subject: Re: [JDBC] Query Time > > > I'd aree with Tom here that it sounds like there's lots of dead tuples in > that table. > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > 30 million rows.... > > > > -----Original Message----- > > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > > Sent: Wednesday, August 13, 2003 4:39 PM > > To: Lufkin, Brad > > Cc: 'psql' > > Subject: Re: [JDBC] Query Time > > > > > > How many rows in that table? 90 seconds is a long time for a select to > > take, period, with or without a limit. > > > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > > > Thanks for the info. Still, I'm surprised that a limited query would > take > > 90 > > > seconds. > > > > > > -----Original Message----- > > > From: scott.marlowe [mailto:scott.marlowe@ihs.com] > > > Sent: Wednesday, August 13, 2003 4:20 PM > > > To: Lufkin, Brad > > > Cc: 'psql' > > > Subject: Re: [JDBC] Query Time > > > > > > > > > On Wed, 13 Aug 2003, Lufkin, Brad wrote: > > > > > > > I'm running the following query: > > > > > > > > SELECT * FROM someTable LIMIT 20 > > > > > > > > I turned on explain analyze (tip #8!) and was told that the query plan > > was > > > > sequential (no surprise there) with an estimated cost of between 0.00 > > and > > > > 1.07. Surprisingly, the actual time was around 90000 msec (or > > > one-and-a-half > > > > minutes). What's going on? > > > > > > costs are estimated as a percentage of a cost of a single page access in > > > > sequential mode. I.e. a single sequential page access is assumed to > cost > > > 1.0, and everything is relative to that. The cost numbers do NOT > > > translate directly into any time unit. > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > >