pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCan this query go faster???
14+ messages / 8 participants
[nested] [flat]
* Can this query go faster???
@ 2005-12-06 09:43 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 3 replies; 14+ messages in thread
From: Joost Kraaijeveld @ 2005-12-06 09:43 UTC (permalink / raw)
To: pgsql-performance
Hi,
Is it possible to get this query run faster than it does now, by adding
indexes, changing the query?
SELECT customers.objectid FROM prototype.customers, prototype.addresses
WHERE
customers.contactaddress = addresses.objectid
ORDER BY zipCode asc, housenumber asc
LIMIT 1 OFFSET 283745
Explain:
Limit (cost=90956.71..90956.71 rows=1 width=55)
-> Sort (cost=90247.34..91169.63 rows=368915 width=55)
Sort Key: addresses.zipcode, addresses.housenumber
-> Hash Join (cost=14598.44..56135.75 rows=368915 width=55)
Hash Cond: ("outer".contactaddress = "inner".objectid)
-> Seq Scan on customers (cost=0.00..31392.15
rows=368915 width=80)
-> Hash (cost=13675.15..13675.15 rows=369315 width=55)
-> Seq Scan on addresses (cost=0.00..13675.15
rows=369315 width=55)
The customers table has an index on contactaddress and objectid.
The addresses table has an index on zipcode+housenumber and objectid.
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 09:51 Michael Riess <mlriess@gmx.de>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 0 replies; 14+ messages in thread
From: Michael Riess @ 2005-12-06 09:51 UTC (permalink / raw)
To: pgsql-performance
> Hi,
>
> Is it possible to get this query run faster than it does now, by adding
> indexes, changing the query?
>
> SELECT customers.objectid FROM prototype.customers, prototype.addresses
> WHERE
> customers.contactaddress = addresses.objectid
> ORDER BY zipCode asc, housenumber asc
> LIMIT 1 OFFSET 283745
>
> Explain:
>
> Limit (cost=90956.71..90956.71 rows=1 width=55)
> -> Sort (cost=90247.34..91169.63 rows=368915 width=55)
> Sort Key: addresses.zipcode, addresses.housenumber
> -> Hash Join (cost=14598.44..56135.75 rows=368915 width=55)
> Hash Cond: ("outer".contactaddress = "inner".objectid)
> -> Seq Scan on customers (cost=0.00..31392.15
> rows=368915 width=80)
> -> Hash (cost=13675.15..13675.15 rows=369315 width=55)
> -> Seq Scan on addresses (cost=0.00..13675.15
> rows=369315 width=55)
>
> The customers table has an index on contactaddress and objectid.
> The addresses table has an index on zipcode+housenumber and objectid.
When the resulting relation contains all the info from both tables,
indexes won't help, seq scan is inevitable.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 09:52 Csaba Nagy <nagy@ecircle-ag.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 2 replies; 14+ messages in thread
From: Csaba Nagy @ 2005-12-06 09:52 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
Joost,
Why do you use an offset here ? I guess you're traversing the table
somehow, in this case it would be better to remember the last zipcode +
housenumber and put an additional condition to get the next bigger than
the last one you've got... that would go for the index on
zipcode+housenumber and be very fast. The big offset forces postgres to
traverse that many entries until it's able to pick the one row for the
result...
On Tue, 2005-12-06 at 10:43, Joost Kraaijeveld wrote:
> Hi,
>
> Is it possible to get this query run faster than it does now, by adding
> indexes, changing the query?
>
> SELECT customers.objectid FROM prototype.customers, prototype.addresses
> WHERE
> customers.contactaddress = addresses.objectid
> ORDER BY zipCode asc, housenumber asc
> LIMIT 1 OFFSET 283745
>
> Explain:
>
> Limit (cost=90956.71..90956.71 rows=1 width=55)
> -> Sort (cost=90247.34..91169.63 rows=368915 width=55)
> Sort Key: addresses.zipcode, addresses.housenumber
> -> Hash Join (cost=14598.44..56135.75 rows=368915 width=55)
> Hash Cond: ("outer".contactaddress = "inner".objectid)
> -> Seq Scan on customers (cost=0.00..31392.15
> rows=368915 width=80)
> -> Hash (cost=13675.15..13675.15 rows=369315 width=55)
> -> Seq Scan on addresses (cost=0.00..13675.15
> rows=369315 width=55)
>
> The customers table has an index on contactaddress and objectid.
> The addresses table has an index on zipcode+housenumber and objectid.
>
> TIA
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> e-mail: J.Kraaijeveld@Askesis.nl
> web: www.askesis.nl
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 09:54 Markus Wollny <Markus.Wollny@computec.de>
0 siblings, 0 replies; 14+ messages in thread
From: Markus Wollny @ 2005-12-06 09:54 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; pgsql-performance
Hi,
> -----Ursprüngliche Nachricht-----
> Von: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] Im Auftrag
> von Joost Kraaijeveld
> Gesendet: Dienstag, 6. Dezember 2005 10:44
> An: Pgsql-Performance
> Betreff: [PERFORM] Can this query go faster???
> SELECT customers.objectid FROM prototype.customers,
> prototype.addresses WHERE customers.contactaddress =
> addresses.objectid ORDER BY zipCode asc, housenumber asc
> LIMIT 1 OFFSET 283745
>
> Explain:
>
> Limit (cost=90956.71..90956.71 rows=1 width=55)
> -> Sort (cost=90247.34..91169.63 rows=368915 width=55)
> Sort Key: addresses.zipcode, addresses.housenumber
> -> Hash Join (cost=14598.44..56135.75 rows=368915 width=55)
> Hash Cond: ("outer".contactaddress = "inner".objectid)
> -> Seq Scan on customers (cost=0.00..31392.15
> rows=368915 width=80)
> -> Hash (cost=13675.15..13675.15 rows=369315 width=55)
> -> Seq Scan on addresses (cost=0.00..13675.15
> rows=369315 width=55)
>
> The customers table has an index on contactaddress and objectid.
> The addresses table has an index on zipcode+housenumber and objectid.
The planner chooses sequential scans on customers.contactaddress and addresses.objectid instead of using the indices. In order to determine whether this is a sane decision, you should run EXPLAIN ANALYZE on this query, once with SET ENABLE_SEQSCAN = on; and once with SET ENABLE_SEQSCAN = off;. If the query is significantly faster with SEQSCAN off, then something is amiss - either you haven't run analyze often enough so the stats are out of date or you have random_page_cost set too high (look for the setting in postgresql.conf) - these two are the "usual suspects".
Kind regards
Markus
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 10:21 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Csaba Nagy <nagy@ecircle-ag.com>
1 sibling, 1 reply; 14+ messages in thread
From: Joost Kraaijeveld @ 2005-12-06 10:21 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: pgsql-performance
On Tue, 2005-12-06 at 10:52 +0100, Csaba Nagy wrote:
> Joost,
>
> Why do you use an offset here ? I guess you're traversing the table
> somehow, in this case it would be better to remember the last zipcode +
> housenumber and put an additional condition to get the next bigger than
> the last one you've got... that would go for the index on
> zipcode+housenumber and be very fast. The big offset forces postgres to
> traverse that many entries until it's able to pick the one row for the
I am forced to translate a sorting dependent record number to a record
in the database. The GUI (a Java JTable) works with record /row numbers,
which is handy if one has an ISAM database, but not if one uses
PostgreSQL.
I wonder if using a forward scrolling cursor would be faster.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 10:32 Tino Wildenhain <tino@wildenhain.de>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 14+ messages in thread
From: Tino Wildenhain @ 2005-12-06 10:32 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; pgsql-performance
Joost Kraaijeveld schrieb:
> On Tue, 2005-12-06 at 10:52 +0100, Csaba Nagy wrote:
>
>>Joost,
>>
>>Why do you use an offset here ? I guess you're traversing the table
>>somehow, in this case it would be better to remember the last zipcode +
>>housenumber and put an additional condition to get the next bigger than
>>the last one you've got... that would go for the index on
>>zipcode+housenumber and be very fast. The big offset forces postgres to
>>traverse that many entries until it's able to pick the one row for the
>
> I am forced to translate a sorting dependent record number to a record
> in the database. The GUI (a Java JTable) works with record /row numbers,
> which is handy if one has an ISAM database, but not if one uses
> PostgreSQL.
You can have a row number in postgres easily too. For example if you
just include a serial for the row number.
Cursor would work too but you would need to have a persistent connection.
Regards
Tino
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 11:21 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 1 reply; 14+ messages in thread
From: Joost Kraaijeveld @ 2005-12-06 11:21 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; pgsql-performance
Hi Tino,
On Tue, 2005-12-06 at 11:32 +0100, Tino Wildenhain wrote:
> You can have a row number in postgres easily too. For example if you
> just include a serial for the row number.
Not if the order of things is determined runtime and not at insert time...
> Cursor would work too but you would need to have a persistent connection.
I just tried it: a cursor is not faster (what does not surprise me at
all, as the amount of work looks the same to me)
I guess there is no solution.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 11:36 Tino Wildenhain <tino@wildenhain.de>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 14+ messages in thread
From: Tino Wildenhain @ 2005-12-06 11:36 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; pgsql-performance
Joost Kraaijeveld schrieb:
> Hi Tino,
>
..
>
>>Cursor would work too but you would need to have a persistent connection.
>
> I just tried it: a cursor is not faster (what does not surprise me at
> all, as the amount of work looks the same to me)
Actually no, if you scroll forward, you just ask the database for the
next rows to materialize. So if you are ahead in your database and
ask for next rows, it should be faster then working w/ an offset
from start each time.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 12:20 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 2 replies; 14+ messages in thread
From: Joost Kraaijeveld @ 2005-12-06 12:20 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; pgsql-performance
On Tue, 2005-12-06 at 12:36 +0100, Tino Wildenhain wrote:
> >
> > I just tried it: a cursor is not faster (what does not surprise me at
> > all, as the amount of work looks the same to me)
>
> Actually no, if you scroll forward, you just ask the database for the
> next rows to materialize. So if you are ahead in your database and
> ask for next rows, it should be faster then working w/ an offset
> from start each time.
Ah, a misunderstanding: I only need to calculate an index if the user
wants a record that is not in or adjacent to the cache (in which case I
can do a "select values > last value in the cache". So I must always
materialize all rows below the wanted index.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 12:30 Tino Wildenhain <tino@wildenhain.de>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 0 replies; 14+ messages in thread
From: Tino Wildenhain @ 2005-12-06 12:30 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Csaba Nagy <nagy@ecircle-ag.com>; pgsql-performance
Joost Kraaijeveld schrieb:
> On Tue, 2005-12-06 at 12:36 +0100, Tino Wildenhain wrote:
>
>>>I just tried it: a cursor is not faster (what does not surprise me at
>>>all, as the amount of work looks the same to me)
>>
>>Actually no, if you scroll forward, you just ask the database for the
>>next rows to materialize. So if you are ahead in your database and
>>ask for next rows, it should be faster then working w/ an offset
>>from start each time.
>
> Ah, a misunderstanding: I only need to calculate an index if the user
> wants a record that is not in or adjacent to the cache (in which case I
> can do a "select values > last value in the cache". So I must always
> materialize all rows below the wanted index.
>
Yes, but still advancing a few blocks from where the cursor is
should be faster then re-issuing the query and scroll thru
the whole resultset to where you want to go.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 12:32 Csaba Nagy <nagy@ecircle-ag.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
1 sibling, 0 replies; 14+ messages in thread
From: Csaba Nagy @ 2005-12-06 12:32 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
On Tue, 2005-12-06 at 13:20, Joost Kraaijeveld wrote:
[snip]
> Ah, a misunderstanding: I only need to calculate an index if the user
> wants a record that is not in or adjacent to the cache (in which case I
> can do a "select values > last value in the cache". So I must always
> materialize all rows below the wanted index.
In this case the query will very likely not work faster. It must always
visit all the records till the required offset. If the plan should be
faster using the index, then you probably need to analyze (I don't
recall from your former posts if you did it recently or not), in any
case you could check an "explain analyze" to see if the planner is
mistaken or not - you might already know this.
Cheers,
Csaba.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 13:41 Ron <rjpeace@earthlink.net>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2 siblings, 0 replies; 14+ messages in thread
From: Ron @ 2005-12-06 13:41 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; pgsql-performance
At 04:43 AM 12/6/2005, Joost Kraaijeveld wrote:
>Hi,
>
>Is it possible to get this query run faster than it does now, by adding
>indexes, changing the query?
>
>SELECT customers.objectid FROM prototype.customers, prototype.addresses
>WHERE
>customers.contactaddress = addresses.objectid
>ORDER BY zipCode asc, housenumber asc
>LIMIT 1 OFFSET 283745
>
>Explain:
>
>Limit (cost=90956.71..90956.71 rows=1 width=55)
> -> Sort (cost=90247.34..91169.63 rows=368915 width=55)
> Sort Key: addresses.zipcode, addresses.housenumber
> -> Hash Join (cost=14598.44..56135.75 rows=368915 width=55)
> Hash Cond: ("outer".contactaddress = "inner".objectid)
> -> Seq Scan on customers (cost=0.00..31392.15
>rows=368915 width=80)
> -> Hash (cost=13675.15..13675.15 rows=369315 width=55)
> -> Seq Scan on addresses (cost=0.00..13675.15
>rows=369315 width=55)
>
>The customers table has an index on contactaddress and objectid.
>The addresses table has an index on zipcode+housenumber and objectid.
>
>TIA
customer names, customers.objectid, addresses, and addresses.objectid
should all be static (addresses do not change, just the customers
associated with them; and once a customer has been assigned an id
that better never change...).
To me, this sounds like the addresses and customers tables should be
duplicated and then physically laid out in sorted order by
<tablename>.objectid in one set and by the "human friendly"
associated string in the other set.
Then a finding a specific <tablename>.objectid or it's associated
string can be done in at worse O(lgn) time assuming binary search
instead of O(n) time for a sequential scan. If pg is clever enough,
it might be able to do better than that.
IOW, I'd try duplicating the addresses and customers tables and using
the appropriate CLUSTERed Index on each.
I know this breaks Normal Form. OTOH, this kind of thing is common
practice for data mining problems on static or almost static data.
Hope this is helpful,
Ron
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 13:58 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 14+ messages in thread
From: Merlin Moncure @ 2005-12-06 13:58 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
> On Tue, 2005-12-06 at 11:32 +0100, Tino Wildenhain wrote:
> > You can have a row number in postgres easily too. For example if you
> > just include a serial for the row number.
> Not if the order of things is determined runtime and not at insert
time...
>
> > Cursor would work too but you would need to have a persistent
> connection.
> I just tried it: a cursor is not faster (what does not surprise me at
> all, as the amount of work looks the same to me)
>
> I guess there is no solution.
>
sure there is. This begs the question: 'why do you want to read exactly
283745 rows ahead of row 'x'?) :)
If you are scrolling forwards in a set, just pull in, say, 100-1000 rows
at a time, ordered, and grab the next 1000 based on the highest value
read previously.
You can do this on server side (cursor) or client side (parameterized
query). There are advantages and disadvantages to each. If you are
looping over this set and doing processing, a cursor would be ideal (try
out pl/pgsql).
Welcome to PostgreSQL! :)
Merlin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Can this query go faster???
@ 2005-12-06 20:50 Bruno Wolff III <bruno@wolff.to>
parent: Csaba Nagy <nagy@ecircle-ag.com>
1 sibling, 0 replies; 14+ messages in thread
From: Bruno Wolff III @ 2005-12-06 20:50 UTC (permalink / raw)
To: Csaba Nagy <nagy@ecircle-ag.com>; +Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; pgsql-performance
On Tue, Dec 06, 2005 at 10:52:57 +0100,
Csaba Nagy <nagy@ecircle-ag.com> wrote:
> Joost,
>
> Why do you use an offset here ? I guess you're traversing the table
> somehow, in this case it would be better to remember the last zipcode +
> housenumber and put an additional condition to get the next bigger than
> the last one you've got... that would go for the index on
> zipcode+housenumber and be very fast. The big offset forces postgres to
> traverse that many entries until it's able to pick the one row for the
> result...
The other problem with saving an offset, is unless the data isn't changing
or you are doing all of the searches in one serialized transaction, the
fixed offset might not put you back where you left off.
Using the last key, instead of counting records is normally a better way
to do this.
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2005-12-06 20:50 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-06 09:43 Can this query go faster??? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-12-06 09:51 ` Michael Riess <mlriess@gmx.de>
2005-12-06 09:52 ` Csaba Nagy <nagy@ecircle-ag.com>
2005-12-06 10:21 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-12-06 10:32 ` Tino Wildenhain <tino@wildenhain.de>
2005-12-06 11:21 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-12-06 11:36 ` Tino Wildenhain <tino@wildenhain.de>
2005-12-06 12:20 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-12-06 12:30 ` Tino Wildenhain <tino@wildenhain.de>
2005-12-06 12:32 ` Csaba Nagy <nagy@ecircle-ag.com>
2005-12-06 20:50 ` Bruno Wolff III <bruno@wolff.to>
2005-12-06 13:41 ` Ron <rjpeace@earthlink.net>
2005-12-06 09:54 Re: Can this query go faster??? Markus Wollny <Markus.Wollny@computec.de>
2005-12-06 13:58 Re: Can this query go faster??? Merlin Moncure <merlin.moncure@rcsonline.com>
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