I have a table with a large number of rows (10K in the example below,
but >1M in some databases). I would like to find the distinct
values for one of the columns. The column is indexed.
I would have expected that this would be a very fast operation,
simply walking down the index. In the example below, there is
only 1 unique value, but it takes 2 seconds. I would have
expected more like ~50ms.
explain analyze select distinct element from elem_trafficstats ;
NOTICE: QUERY PLAN:
Unique (cost=0.00..4117.18 rows=9350 width=44) (actual time=0.59..1710.34
rows=1 loops=1)
-> Index Scan using elem_trafficstats_element_idx on elem_trafficstats
(cost=0.00..3883.44 rows=93495 width=44) (actual time=0.58..1184.17
rows=93495 loops=1)
Total runtime: 1710.88 msec
is there an alternate way to construct a 'distinct' query
that will use the index properly?
--don
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings 1 3235
On Wed, Jan 28, 2004 at 11:20:30PM -0500, Don Bowman wrote: I have a table with a large number of rows (10K in the example below, but >1M in some databases). I would like to find the distinct values for one of the columns. The column is indexed. I would have expected that this would be a very fast operation, simply walking down the index. In the example below, there is only 1 unique value, but it takes 2 seconds. I would have expected more like ~50ms.
The problem is that the index doesn't contain info about which rows are
visibile in your current transaction, so it has to load the entire table to
check. Looks like it used the index to avoid a sort step. I don't think
there is a way to write this that doesn't need the whole table.
Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers anywhere, to being about 20% done. Sweet. And the last 80% usually takes 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQFAHF+PY5Twig3Ge+YRAnKHAKDCSJtvMmoBQw0aFyW0lu vfhgASmgCbB1Rc
s1u49h+ZlpLAtq57Rnhpg2U=
=R37U
-----END PGP SIGNATURE----- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Perre Van Wilrijk |
last post by:
Hi,
I have a table as following
aa Text1 aa, p@xxx.be, 15267
aa Text1 aa, p@xxx.be, 16598
aa Text1 aa, p@xxx.be, 17568
aa Text2 aa, p@xxx.be, 25698
aa Text3 aa, x@zzz.be, 12258
|
by: MJunium |
last post by:
I hope someone can help me with this one. For performance reasons, I have a
denormalized database. There are two tables in the database we can call
them table a and table b. Both of theses...
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: Brian |
last post by:
Hello All -
I am wondering if anyone has any thoughts on which is better from a
performance perspective: a nested Select statement or an Inner Join.
For example, I could do either of the...
|
by: mathilda |
last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than
SELECT all other factors being equal. I disagree. We are linking an
Access front end to a SQL Server back end and normally are...
|
by: Izak van Langevelde |
last post by:
I would like to query a database for 3 children whose fathers are the
oldest. The answer isn't necessarily unique: if the oldest father has 6
kids, then any three of his children will do. My first...
|
by: Lone Wolf |
last post by:
We have a recordset with the following structure id (int), mls
(varchar)
What we need to do is within a single sql call we need to show all
records where the mls = '' and all unique records...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Andrew S |
last post by:
Hello Mr. Expert:
- I have 3 tables in mysql in MyISAM table format, I am using mysql4.0
on freebsd5.3
- producttbl, productdetailentbl, pricetblN
- they all have "productid" as the Primary KEY....
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |